package com.authine.cloudpivot.controller;

/**
 * @author yt
 * @date 2023年08月12日 11:11
 */

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.authine.cloudpivot.engine.component.query.api.Page;

import com.authine.cloudpivot.engine.domain.runtime.BizObject;
import com.authine.cloudpivot.engine.open.domain.organization.Role;
import com.authine.cloudpivot.engine.open.domain.organization.RoleUser;
import com.authine.cloudpivot.engine.open.service.OpenEngine;
import com.authine.cloudpivot.foundation.orm.api.dml.JPANativeQuery;
import com.authine.cloudpivot.foundation.orm.api.model.BizObjectQueryObject;
import com.authine.cloudpivot.util.Result;
import com.authine.cloudpivot.web.api.controller.base.BaseController;
import com.authine.cloudpivot.web.api.handler.CustomizedOrigin;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.*;
import java.util.function.Function;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import static com.authine.cloudpivot.engine.service.impl.constant.BusinessRuleConstant.NodeJsonKey.id;
import static com.authine.cloudpivot.web.api.util.AuthUtils.getUserId;

/**
 * 定制sql查询功能Controller.
 */
@Api(tags = "运行时::列表::定制列表查询")
@RestController
@RequestMapping("/api/reportsql/query")
@Slf4j
@CustomizedOrigin(level = 1)
public class QueryReportSqlController extends BaseController {


    @Autowired
    private JPANativeQuery jpaNativeQuery;

    /*
        事件数量统计（数量统计）getEventCount
        传入参数  startDate ,endDate ,deptId
        返回参数List   总事件、未办结、已办结、办结率，满意率
        total,completedCnt,processingCnt,completionRate,satisfactionRate
    */
    @GetMapping("/getEventCount")
    public List<Map<String, Object>> getEventCount(@RequestParam("startDate") String startDate, @RequestParam("endDate") String endDate, @RequestParam("deptId") String deptId) {
        log.info("getEventCount传入startDate :{}", startDate);
        log.info("getEventCount传入endDate :{}", endDate);
        log.info("getEventCount传入deptId :{}", deptId);
        if (StringUtils.isBlank(startDate)) {
            startDate = "1900-01-01 00:00:00";
        }
        if (StringUtils.isBlank(endDate)) {
            endDate = "2099-01-01 00:00:00";
        }
        if (StringUtils.isBlank(deptId)) {
            deptId = "2c9a8e6488e8fca60188e912ee421f97";
        }
        String finalQuerySql = "";
        if (deptId.equals("2c9a8e6488e8fca60188e912ee421f97")) {
            finalQuerySql = String.format("SELECT completedCnt+processingCnt AS total,completedCnt,processingCnt,qjs,rcs, \n" + "100-ifnull (round(processingCnt/(completedCnt+processingCnt)*100,2),0) as completionRate,\n" + "100-ifnull (round(dissatisfactionCnt/cntc*100,2),0) as satisfactionRate FROM ( \n" + "SELECT sum(case when sequenceStatus='COMPLETED' then 1 else 0  end)  AS completedCnt,  \n" + "sum(case when sequenceStatus='PROCESSING' then 1 else 0  end)  processingCnt,\n" +
            " 	 	sum( case when eventStatus = '吹哨' then 1 else 0 end) as qjs,sum( case when eventStatus = '普通'  then 1 else 0 end) as rcs,\n"
            + "sum(CASE WHEN radio1685887886485='不满意' OR myd_shifoumanyi='不满意' THEN 1 ELSE 0 END) AS dissatisfactionCnt, \n" + "sum(CASE WHEN wi.sequenceNo IS NOT NULL THEN 1 ELSE 0 END) AS cntc    \n" + "FROM ikhmj_chuishao_001 c \n" + "left join h_org_department t2 on c.createdDeptId = t2.id and t2.deleted =0 \n" + "LEFT JOIN (SELECT sequenceNo FROM biz_workitem_finished WHERE workflowCode='ce_001' \n" + "AND activityCode IN ('Activity8','Activity9') GROUP BY sequenceNo) wi ON wi.sequenceNo=c.sequenceNo \n" + "WHERE  (c.eventStatus !='删除' or c.eventStatus is null  ) \n" + "and c.sequenceStatus in('PROCESSING','COMPLETED')  \n" + "and c.createdTime BETWEEN '%s' and  '%s'   )a \n", startDate, endDate);
            log.info("sql = :{}", finalQuerySql);
        } else {
            finalQuerySql = String.format("SELECT completedCnt+processingCnt AS total,completedCnt,processingCnt,qjs,rcs, \n" + "100-ifnull (round(processingCnt/(completedCnt+processingCnt)*100,2),0) as completionRate,\n" + "100-ifnull (round(dissatisfactionCnt/cntc*100,2),0) as satisfactionRate FROM ( \n" + "SELECT sum(case when sequenceStatus='COMPLETED' then 1 else 0  end)  AS completedCnt,  \n" + "sum(case when sequenceStatus='PROCESSING' then 1 else 0  end)  processingCnt,\n" +
            " sum( case when eventStatus = '吹哨' then 1 else 0 end) as qjs,sum( case when eventStatus = '普通'  then 1 else 0 end) as rcs,\n"
            + "sum(CASE WHEN radio1685887886485='不满意' OR myd_shifoumanyi='不满意' THEN 1 ELSE 0 END) AS dissatisfactionCnt, \n" + "sum(CASE WHEN wi.sequenceNo IS NOT NULL THEN 1 ELSE 0 END) AS cntc    \n" + "FROM ikhmj_chuishao_001 c \n" + "left join h_org_department t2 on c.createdDeptId = t2.id and t2.deleted =0 \n" + "LEFT JOIN (SELECT sequenceNo FROM biz_workitem_finished WHERE workflowCode='ce_001' \n" + "AND activityCode IN ('Activity8','Activity9') GROUP BY sequenceNo) wi ON wi.sequenceNo=c.sequenceNo \n" + "WHERE  (c.eventStatus !='删除' or c.eventStatus is null  ) \n" + "and c.sequenceStatus in('PROCESSING','COMPLETED')  \n" + "and c.createdTime BETWEEN '%s' and  '%s'    \n" + "and t2.queryCode like (select concat(queryCode,'%s') from h_org_department where id = '%s') ) a ", startDate, endDate, "%", deptId);
            log.info("sql = :{}", finalQuerySql);
        }
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }

    /*
        五零事件统计getTypeSum
        传入参数  startDate ,endDate ,deptId
        返回参数List 总事件、治安零发案、环境零污染、安全零事故、服务零距离、社区安全排查、居民零上访、其他
        total、zaCnt、hjCnt、aqCnt、fwCnt、sqCnt、jmCnt、qtCnt
    */
    @GetMapping("/getTypeSum")
    public List<Map<String, Object>> getTypeSum(@RequestParam("startDate") String startDate, @RequestParam("endDate") String endDate, @RequestParam("deptId") String deptId) {
        log.info("getTypeSum传入startDate :{}", startDate);
        log.info("getTypeSum传入endDate :{}", endDate);
        log.info("getTypeSum传入deptId :{}", deptId);
        if (StringUtils.isBlank(startDate)) {
            startDate = "1900-01-01 00:00:00";
        }
        if (StringUtils.isBlank(endDate)) {
            endDate = "2099-01-01 00:00:00";
        }
        if (StringUtils.isBlank(deptId)) {
            deptId = "2c9a8e6488e8fca60188e912ee421f97";
        }
        String finalQuerySql = "";
        if (deptId.equals("2c9a8e6488e8fca60188e912ee421f97")) {
            finalQuerySql = String.format(" SELECT count(1) as total  \n" + ",sum(case when c.Dropdown1685712329221 ='治安零发案' then 1 else 0 end ) as zaCnt   \n" + ",sum(case when c.Dropdown1685712329221 ='环境零污染' then 1 else 0 end ) as hjCnt   \n" + ",sum(case when c.Dropdown1685712329221 ='安全零事故' then 1 else 0 end ) as aqCnt   \n" + ",sum(case when c.Dropdown1685712329221 ='服务零距离' then 1 else 0 end ) as fwCnt   \n" + ",sum(case when c.Dropdown1685712329221 ='社区安全排查' then 1 else 0 end ) as sqCnt \n" + ",sum(case when c.Dropdown1685712329221 ='居民零上访' then 1 else 0 end ) as  jmCnt  \n" + ",sum(case when c.Dropdown1685712329221 ='其他' then 1 else 0 end ) as qtCnt        \n" + "FROM ikhmj_chuishao_001 c \n" + "LEFT JOIN h_org_user u ON c.owner = u.id \n" + "LEFT JOIN h_org_department d ON c.ownerDeptId = d.id \n" + "LEFT JOIN biz_workflow_instance wi ON c.workflowInstanceId = wi.id \n" + "WHERE ( c.eventStatus != '删除' or  c.eventStatus is null ) and c.sequenceStatus !='DRAFT' \n" + "and c.createdTime BETWEEN '%s' and  '%s'    \n", startDate, endDate);
        } else {
            finalQuerySql = String.format(" SELECT count(1) as total  \n" + ",sum(case when c.Dropdown1685712329221 ='治安零发案' then 1 else 0 end ) as zaCnt   \n" + ",sum(case when c.Dropdown1685712329221 ='环境零污染' then 1 else 0 end ) as hjCnt   \n" + ",sum(case when c.Dropdown1685712329221 ='安全零事故' then 1 else 0 end ) as aqCnt   \n" + ",sum(case when c.Dropdown1685712329221 ='服务零距离' then 1 else 0 end ) as fwCnt   \n" + ",sum(case when c.Dropdown1685712329221 ='社区安全排查' then 1 else 0 end ) as sqCnt \n" + ",sum(case when c.Dropdown1685712329221 ='居民零上访' then 1 else 0 end ) as  jmCnt  \n" + ",sum(case when c.Dropdown1685712329221 ='其他' then 1 else 0 end ) as qtCnt        \n" + "FROM ikhmj_chuishao_001 c \n" + "LEFT JOIN h_org_user u ON c.owner = u.id \n" + "LEFT JOIN h_org_department d ON c.ownerDeptId = d.id \n" + "LEFT JOIN biz_workflow_instance wi ON c.workflowInstanceId = wi.id \n" + "WHERE ( c.eventStatus != '删除' or  c.eventStatus is null ) and c.sequenceStatus !='DRAFT' \n" + "and c.createdTime BETWEEN '%s' and  '%s'    \n" + "and d.queryCode like (select concat(queryCode,'%s') from h_org_department where id = '%s')", startDate, endDate, "%", deptId);
        }
        log.info("sql = :{}", finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }


/*
    发起事件统计getEventSum
    传入参数  startDate ,endDate ,deptId 
    返回参数List  网格事件数量、社区事件数量、街道事件数量、自办自结事件数量、自办自结率、吹哨、吹哨率、传哨
    total, wgCnt, sqCnt, jdCnt, zbCnt, zbzdl, chuishaoCnt, csl, chuanshaoCnt
*/


    @GetMapping("/getEventSum")
    public List<Map<String, Object>> getEventSum(@RequestParam("startDate") String startDate, @RequestParam("endDate") String endDate, @RequestParam("deptId") String deptId) {
        log.info("getTypeSum传入startDate :{}", startDate);
        log.info("getTypeSum传入endDate :{}", endDate);
        log.info("getTypeSum传入deptId :{}", deptId);
        if (StringUtils.isBlank(startDate)) {
            startDate = "1900-01-01 00:00:00";
        }
        if (StringUtils.isBlank(endDate)) {
            endDate = "2099-01-01 00:00:00";
        }
        if (StringUtils.isBlank(deptId)) {
            deptId = "2c9a8e6488e8fca60188e912ee421f97";
        }
        String finalQuerySql = "";
        if (deptId.equals("2c9a8e6488e8fca60188e912ee421f97")) {
            finalQuerySql = String.format(" select  count(1) total \n" + ",sum(case when sswg is not null then 1 else 0 end ) wgCnt \n" + ", sum(case when sssq is not null and sswg is null then 1 else 0 end ) sqCnt \n" + ", sum(case when ssjd is not null and sswg is null and sssq is null then 1 else 0 end ) jdCnt \n" + ", SUM(CASE WHEN wgy_clfs = '自办'  or sq_shangbao = '自办' or  jd_shangbao = '自办' THEN 1 ELSE 0 END ) AS zbCnt \n" + ", round(SUM(CASE WHEN wgy_clfs = '自办'  or sq_shangbao = '自办' or  jd_shangbao = '自办' THEN 1 ELSE 0 END )/count(1)*100,2) zbzdl \n" + ",sum(case when eventStatus ='吹哨' then 1 else 0 end ) as  chuishaoCnt \n" + " ,round(SUM(case when eventStatus ='吹哨' then 1 else 0 end )/count(1)*100,2) csl \n" + ",sum(case when    kf_liuchdaodajiedan is not null then 1 else 0 end ) chuanshaoCnt \n" + "FROM ikhmj_chuishao_001 c \n" + "LEFT JOIN h_org_user u ON c.owner = u.id \n" + "LEFT JOIN h_org_department d ON c.ownerDeptId = d.id and d.deleted =0 \n" + "LEFT JOIN biz_workflow_instance wi ON c.workflowInstanceId = wi.id \n" + "WHERE ( c.eventStatus != '删除' or  c.eventStatus is null ) and c.sequenceStatus !='DRAFT' \n" + "and c.createdTime BETWEEN '%s' and  '%s'    \n", startDate, endDate);
        } else {
            finalQuerySql = String.format(" select  count(1) total \n" + ",sum(case when sswg is not null then 1 else 0 end ) wgCnt \n" + ", sum(case when sssq is not null and sswg is null then 1 else 0 end ) sqCnt \n" + ", sum(case when ssjd is not null and sswg is null and sssq is null then 1 else 0 end ) jdCnt \n" + ", SUM(CASE WHEN wgy_clfs = '自办'  or sq_shangbao = '自办' or  jd_shangbao = '自办' THEN 1 ELSE 0 END ) AS zbCnt \n" + ", round(SUM(CASE WHEN wgy_clfs = '自办'  or sq_shangbao = '自办' or  jd_shangbao = '自办' THEN 1 ELSE 0 END )/count(1)*100,2) zbzdl \n" + ",sum(case when eventStatus ='吹哨' then 1 else 0 end ) as  chuishaoCnt \n" + " ,round(SUM(case when eventStatus ='吹哨' then 1 else 0 end )/count(1)*100,2) csl \n" + ",sum(case when    kf_liuchdaodajiedan is not null then 1 else 0 end ) chuanshaoCnt \n" + "FROM ikhmj_chuishao_001 c \n" + "LEFT JOIN h_org_user u ON c.owner = u.id \n" + "LEFT JOIN h_org_department d ON c.ownerDeptId = d.id and d.deleted =0 \n" + "LEFT JOIN biz_workflow_instance wi ON c.workflowInstanceId = wi.id \n" + "WHERE ( c.eventStatus != '删除' or  c.eventStatus is null ) and c.sequenceStatus !='DRAFT' \n" + "and c.createdTime BETWEEN '%s' and  '%s'    \n" + "and d.queryCode like (select concat(queryCode,'%s') from h_org_department where id = '%s')", startDate, endDate, "%", deptId);
        }
        log.info("sql = :{}", finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);

    }

/*
    事件统计getEventList
    传入参数 
    startDate ,endDate ,deptId ,type='全部、普通、紧急、重难点事件、呈报领导事件'
    返回参数List 
    ID，流水号，标题，事件主题，状态
    id,sequenceNo，title,theme,status
*/

    @GetMapping("/getEventList")
    public List<Map<String, Object>> getEventList(@RequestParam("startDate") String startDate, @RequestParam("endDate") String endDate, @RequestParam("deptId") String deptId, @RequestParam("type") String type) {

        if (StringUtils.isBlank(startDate)) {
            startDate = "1900-01-01 00:00:00";
        }
        if (StringUtils.isBlank(endDate)) {
            endDate = "2099-01-01 00:00:00";
        }
        if (StringUtils.isBlank(deptId)) {
            deptId = "2c9a8e6488e8fca60188e912ee421f97";
        }
        log.info("getEventList传入startDate :{}", startDate);
        log.info("getEventList传入endDate :{}", endDate);
        log.info("getEventList传入deptId :{}", deptId);
        String finalQuerySql = "";
        if (deptId.equals("2c9a8e6488e8fca60188e912ee421f97")) {
            finalQuerySql = String.format("select c.id,c.sequenceNo,c.name as title,wi.id as workflowInstanceId, \n" + "case when c.sequenceStatus='COMPLETED' then '已完结' else '处理中' end as status, \n" + "concat(Dropdown1685712329221,'-',Dropdown1685712342343) as theme \n" + " FROM ikhmj_chuishao_001 c \n" + "LEFT JOIN h_org_user u ON c.owner = u.id \n" + "LEFT JOIN h_org_department d ON c.ownerDeptId = d.id \n" + "LEFT JOIN biz_workflow_instance wi ON c.workflowInstanceId = wi.id \n" + "WHERE ( c.eventStatus != '删除' or  c.eventStatus is null ) and c.sequenceStatus !='DRAFT' \n" + "and c.createdTime BETWEEN '%s' and  '%s'    \n", startDate, endDate);
        } else {
            finalQuerySql = String.format("select c.id,c.sequenceNo,c.name as title,wi.id as workflowInstanceId, \n" + "case when c.sequenceStatus='COMPLETED' then '已完结' else '处理中' end as status, \n" + "concat(Dropdown1685712329221,'-',Dropdown1685712342343) as theme \n" + " FROM ikhmj_chuishao_001 c \n" + "LEFT JOIN h_org_user u ON c.owner = u.id \n" + "LEFT JOIN h_org_department d ON c.ownerDeptId = d.id \n" + "LEFT JOIN biz_workflow_instance wi ON c.workflowInstanceId = wi.id \n" + "WHERE ( c.eventStatus != '删除' or  c.eventStatus is null ) and c.sequenceStatus !='DRAFT' \n" + "and c.createdTime BETWEEN '%s' and  '%s'    \n" + "and d.queryCode like (select concat(queryCode,'%s') from h_org_department where id = '%s')", startDate, endDate, "%", deptId);
        }
        switch (type) {
            case "全部":
                break;
            case "普通":
                finalQuerySql += " and  (sq_jinjichengdu ='普通' or jd_jinjichengdu ='普通' or radio1685886668536 ='普通')";
                break;
            case "紧急":
                finalQuerySql += " and  (sq_jinjichengdu ='紧急' or jd_jinjichengdu ='紧急' or radio1685886668536 ='紧急')";
                break;
            case "重难点事件":
                finalQuerySql += " and  zhzx_chulidanwei = '暂存或重难点问题专班调度，待办理后抄送相关单位' ";
                break;
            case "呈报领导事件":
                finalQuerySql += " and  zhzx_chulidanwei = '呈报区领导'";
                break;
        }
        finalQuerySql += "order BY c.createdTime  limit 200";

        log.info("sql = :{}", finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }

/*
    人数统计getPersonSum
    传入参数 
    deptId  
    返回参数List 
    总人数，网格力量，委办局人数
    total ,wgllSum,wbjSum
*/

    @GetMapping("/getPersonSum")
    public List<Map<String, Object>> getPersonSum(@RequestParam("id") String id) {
        log.info("getWgllSum传入id:{}", id);
        String finalQuerySql = null;
        if ("2c9a8e6488e8fca60188e912ee421f97".equals(id)||StringUtils.isBlank(id)) {
            finalQuerySql = "SELECT \n" +
                    "    COUNT(*) AS total,\n" +
                    "    SUM(CASE WHEN wbdw IS NULL THEN 1 ELSE 0 END) AS wgllSum,\n" +
                    "    SUM(CASE WHEN wbdw IS NOT NULL THEN 1 ELSE 0 END) AS wbjwSum\n" +
                    "FROM ikhmj_txl";
        }else{
            finalQuerySql = String.format("SELECT \n" +
                    "    COUNT(*) AS total,\n" +
                    "    SUM(CASE WHEN wbdw IS NOT NULL THEN 1 ELSE 0 END) AS wbjwSum,\n" +
                    "    SUM(CASE WHEN jd.id = '%s' OR sq.id = '%s' OR wg.id = '%s' THEN 1 ELSE 0 END) AS wgllSum\n" +
                    "FROM ikhmj_txl t\n" +
                    "LEFT JOIN h_org_department jd ON t.jd = jd.id \n" +
                    "LEFT JOIN h_org_department sq ON t.sq = sq.id \n" +
                    "LEFT JOIN h_org_department wg ON t.wg = wg.id;",id,id,id);
        }
        log.info("sql = :{}", finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }

    /*
        街道办结率排名getJdCompletionRateRank
        传入参数
        startDate ,endDate
        返回参数List
        街道名称 排名，办结率
        name,rank,jdbjl
    */
    @GetMapping("/getJdCompletionRateRank")
    public List<Map<String, Object>> getJdCompletionRateRank(@RequestParam("startDate") String startDate, @RequestParam("endDate") String endDate) {
        log.info("getJdCompletionRateRank传入startDate :{}", startDate);
        log.info("getJdCompletionRateRank传入endDate :{}", endDate);
        if (StringUtils.isBlank(startDate)) {
            startDate = "1900-01-01 00:00:00";
        }
        if (StringUtils.isBlank(endDate)) {
            endDate = "2099-01-01 00:00:00";
        }
        String finalQuerySql = String.format(" select  c.ssjd \n" + ",ROUND(sum(case when sequenceStatus ='COMPLETED' then 1 else 0 end )/count(1)*100,2) jdbjl \n" + ",(RANK() OVER(order by (sum(case when sequenceStatus ='COMPLETED' then 1 else 0 end )/count(1)) desc) )as pank \n" + " FROM ikhmj_chuishao_001 c \n" + "LEFT JOIN h_org_user u ON c.owner = u.id \n" + "LEFT JOIN h_org_department d ON c.ownerDeptId = d.id and d.deleted =0 \n" + "LEFT JOIN biz_workflow_instance wi ON c.workflowInstanceId = wi.id \n" + "WHERE ( c.eventStatus != '删除' or  c.eventStatus is null ) and c.sequenceStatus !='DRAFT' \n" + "and c.createdTime BETWEEN '%s' and  '%s'    \n" + "and  ssjd is not null \n" + "group by c.ssjd", startDate, endDate);

        log.info("sql = :{}", finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }


    /*
        委办局数据统计 getWwbSum
        传入参数
        startDate ,endDate
        返回参数List
        普通事件办结率,紧急事件办结率,满意率，总数量,未办结,已办结,办结率
        ptCompletedCnt , jjCompletedCnt , satisfactionRate ,   total  , completedCnt  ,  processingCnt ,  completionRate
    */
    @GetMapping("/getWwbSum")
    public List<Map<String, Object>> getWwbSum(@RequestParam("startDate") String startDate, @RequestParam("endDate") String endDate, @RequestParam("deptId") String deptId) {
        log.info("getWwbSum传入startDate :{}", startDate);
        log.info("getWwbSum传入endDate :{}", endDate);
        log.info("getWwbSum传入deptId :{}", deptId);

        if (StringUtils.isBlank(startDate)) {
            startDate = "1900-01-01 00:00:00";
        }
        if (StringUtils.isBlank(endDate)) {
            endDate = "2099-01-01 00:00:00";
        }
        if (StringUtils.isBlank(deptId)) {
            deptId = "2c9a8e6488e8fca60188e912ee421f97";
        }
        String finalQuerySql = "";
        if (deptId.equals("2c9a8e6488e8fca60188e912ee421f97")) {
            finalQuerySql = String.format("select ROUND(ptsjwj/ptsjSum*100,2) as ptCompletedCnt \n" 
            + ", ROUND(jjsjwj/jjsjSum*100,2) as jjCompletedCnt \n" 
            + ", ROUND(mySum/wcSum*100,2) as satisfactionRate \n" 
            + ", total  \n" 
            + ", wcsum as completedCnt \n" 
            + ", wwcsum as processingCnt \n" 
            + ", ROUND(wcsum/total*100,2) as completionRate \n" 
            + " from ( \n" 
            + "select  \n" 
            + "sum(case when sq_jinjichengdu = '普通' and jd_jinjichengdu ='普通' and c.sequenceStatus ='COMPLETED' then 1 else 0 end ) ptsjwj  \n" 
            + ",sum(case when sq_jinjichengdu = '普通' and jd_jinjichengdu ='普通'   then 1 else 0 end ) ptsjSum \n" 
            + ",sum(case when (sq_jinjichengdu = '紧急' or jd_jinjichengdu ='紧急') and c.sequenceStatus ='COMPLETED' then 1 else 0 end ) jjsjwj  \n" 
            + ",sum(case when sq_jinjichengdu = '紧急' or jd_jinjichengdu ='紧急'   then 1 else 0 end ) jjsjSum \n" 
            + ",sum(case when c.sequenceStatus ='COMPLETED'  then 1 else 0 end ) wcSum \n" 
            + ",sum(case when c.sequenceStatus ='COMPLETED' and radio1685887886485 ='满意' and myd_shifoumanyi ='满意' then 1 else 0 end) mySum  \n" 
            + ",count(1) total \n" 
            + ",sum(case when c.sequenceStatus !='COMPLETED'  then 1 else 0 end ) wwcSum \n" 
            + "FROM ikhmj_chuishao_001 c  \n" 
            + "LEFT JOIN h_org_user u ON c.owner = u.id  \n" 
            + "LEFT JOIN h_org_department d ON c.ownerDeptId = d.id and d.deleted =0  \n" 
            + "LEFT JOIN biz_workflow_instance wi ON c.workflowInstanceId = wi.id  \n" 
            + "WHERE ( c.eventStatus != '删除' or  c.eventStatus is null ) and c.sequenceStatus !='DRAFT' \n" 
            + "and c.createdTime BETWEEN '%s' and  '%s'    \n" 
            + "and kf_liuchdaodajiedan is not null \n" + ") a "
            , startDate, endDate);
            log.info("sql = :{}", finalQuerySql);
            return this.jpaNativeQuery.getList(finalQuerySql, null, null);
        } else {
            finalQuerySql = String.format("select ROUND(ptsjwj/ptsjSum*100,2) as ptCompletedCnt \n" + ", ROUND(jjsjwj/jjsjSum*100,2) as jjCompletedCnt \n" + ", ROUND(mySum/wcSum*100,2) as satisfactionRate \n" + ", total  \n" + ", wcsum as completedCnt \n" + ", wwcsum as processingCnt \n" + ", ROUND(wcsum/total*100,2) as completionRate \n" + " from ( \n" + "select  \n" + "sum(case when sq_jinjichengdu = '普通' and jd_jinjichengdu ='普通' and c.sequenceStatus ='COMPLETED' then 1 else 0 end ) ptsjwj  \n" + ",sum(case when sq_jinjichengdu = '普通' and jd_jinjichengdu ='普通'   then 1 else 0 end ) ptsjSum \n" + ",sum(case when (sq_jinjichengdu = '紧急' or jd_jinjichengdu ='紧急') and c.sequenceStatus ='COMPLETED' then 1 else 0 end ) jjsjwj  \n" + ",sum(case when sq_jinjichengdu = '紧急' or jd_jinjichengdu ='紧急'   then 1 else 0 end ) jjsjSum \n" + ",sum(case when c.sequenceStatus ='COMPLETED'  then 1 else 0 end ) wcSum \n" + ",sum(case when c.sequenceStatus ='COMPLETED' and radio1685887886485 ='满意' and myd_shifoumanyi ='满意' then 1 else 0 end) mySum  \n" + ",count(1) total \n" + ",sum(case when c.sequenceStatus !='COMPLETED'  then 1 else 0 end ) wwcSum \n" + "FROM ikhmj_chuishao_001 c  \n" + "LEFT JOIN h_org_user u ON c.owner = u.id  \n" + "LEFT JOIN h_org_department d ON c.ownerDeptId = d.id and d.deleted =0  \n" + "LEFT JOIN biz_workflow_instance wi ON c.workflowInstanceId = wi.id  \n" + "WHERE ( c.eventStatus != '删除' or  c.eventStatus is null ) and c.sequenceStatus !='DRAFT' \n" + "and c.createdTime BETWEEN '%s' and  '%s'    \n" + "and kf_liuchdaodajiedan is not null \n" + "and d.queryCode like (select concat(queryCode,'%s') from h_org_department where id = '%s') \n" + ") a ", startDate, endDate, "%", deptId);
            log.info("sql = :{}", finalQuerySql);
            return this.jpaNativeQuery.getList(finalQuerySql, null, null);
        }
    }


    /*
        委办局耗时排名getWbjUseTimeRank
        传入参数
        startDate ,endDate
        返回参数List
        委办单位 排名，用时
        name,rank,useTime
    */
    @GetMapping("/getWbjUseTimeRank")
    public List<Map<String, Object>> getWbjUseTimeRank(@RequestParam("startDate") String startDate, @RequestParam("endDate") String endDate) {
        log.info("getJdCompletionRateRank传入startDate :{}", startDate);
        log.info("getJdCompletionRateRank传入endDate :{}", endDate);
        if (StringUtils.isBlank(startDate)) {
            startDate = "1900-01-01 00:00:00";
        }
        if (StringUtils.isBlank(endDate)) {
            endDate = "2099-01-01 00:00:00";
        }
        String finalQuerySql = String.format(
                "SELECT name, " +
                        "CONCAT( " +
                        "CASE WHEN ROUND(AVG(usedTime)) >= 1440 THEN CONCAT(FLOOR(ROUND(AVG(usedTime)) / 1440), '天') ELSE '' END, " +
                        "CASE WHEN ROUND(AVG(usedTime)) >= 60 THEN CONCAT(FLOOR((ROUND(AVG(usedTime)) %% 1440) / 60), '小时') ELSE '' END, " +
                        "CASE WHEN ROUND(AVG(usedTime)) %% 60 > 0 THEN CONCAT(ROUND(AVG(usedTime)) %% 60, '分钟') ELSE '' END, " +
                        "CASE WHEN ROUND(AVG(usedTime)) = 0 THEN CONCAT(0, '分钟') ELSE '' END " +
                        ") AS useTime, " +
                        "(RANK() OVER (ORDER BY AVG(usedTime))) AS pank " +
                        "FROM ( " +
                        "SELECT dd.name AS name, TIMESTAMPDIFF(MINUTE, wf.startTime, wf.finishTime) AS usedTime " +  // 修改1：DATEDIFF->TIMESTAMPDIFF
                        "FROM ikhmj_chuishao_001 c " +
                        "LEFT JOIN h_org_user u ON c.owner = u.id " +
                        "LEFT JOIN h_org_department d ON c.ownerDeptId = d.id AND d.deleted = 0 " +
                        "LEFT JOIN biz_workflow_instance wi ON c.workflowInstanceId = wi.id " +
                        "INNER JOIN biz_workitem_finished wf ON wi.sequenceNo = wf.sequenceNo " +
                        "AND wf.activityCode = 'Activity13' " +
                        "AND wf.approval = 'AGREE' " +
                        "LEFT JOIN h_org_user uu ON wf.participant = uu.id " +
                        "LEFT JOIN h_org_department dd ON uu.departmentId = dd.id " +
                        "WHERE (c.eventStatus != '删除' OR c.eventStatus IS NULL) " +
                        "AND c.sequenceStatus != 'DRAFT' " +
                        "AND c.createdTime BETWEEN '%s' AND '%s' " +

                        "UNION ALL " +

                        "SELECT dd.name AS name, TIMESTAMPDIFF(MINUTE, bwi.startTime, bwi.finishTime) AS usedTime " +  // 保持TIMESTAMPDIFF
                        "FROM ikhmj_chuishao_001 c " +
                        "INNER JOIN biz_workflow_instance wi ON c.workflowInstanceId = wi.id " +
                        "INNER JOIN biz_workflow_instance bwi ON bwi.parentId = wi.id " +
                        "AND bwi.state = 'COMPLETED' " +
                        "LEFT JOIN h_org_user uu ON bwi.originator = uu.id " +
                        "LEFT JOIN h_org_department dd ON uu.departmentId = dd.id " +
                        "WHERE (c.eventStatus != '删除' OR c.eventStatus IS NULL) " +
                        "AND c.sequenceStatus != 'DRAFT' " +
                        "AND c.createdTime BETWEEN '%s' AND '%s' " +
                        ") a " +
                        "GROUP BY name",
                startDate, endDate,  // 第一个BETWEEN参数
                startDate, endDate   // 第二个BETWEEN参数
        );


        log.info("sql = :{}", finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }


    @GetMapping("/getJdJwd")
    public List<Map<String, Object>> getJdJwd(@RequestParam("parentId") String parentId) {

        if (StringUtils.isEmpty(parentId)) {
            parentId = "2c9a8e6488e8fca60188e912ee421f97";
        }

        String finalQuerySql = String.format("select  t1.jdid,t1.sqid,t2.id,t2.name ,case when t3.name='街道' then '开福区' else concat(ifnull(t4.name,''),t3.name) end  as parentName,t2.parentId,t1.jwd from  ilrv7_jwdsj t1 left join h_org_department t2 \n" + " on t1.bm = t2.id  \n" + "  left join h_org_department t3 on t2.parentId = t3.id  \n" + "  left join h_org_department t4 on t3.parentId = t4.id  and t4.id <> '2c9a8e6488e8fca60188e912ee421f97'  \n" + "where t2.parentId = '%s'", parentId);
        log.info("sql = :{}", finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }

    @GetMapping("/getSqJwd")
    public List<Map<String, Object>> getSqJwd(@RequestParam("parentId") String parentId) {

        if (StringUtils.isEmpty(parentId)) {
            parentId = "2c9a8e6488e8fca60188e912ee421f97";
        }

        String finalQuerySql = String.format("select  t1.jdid,t1.sqid,t2.id,concat(t3.NAME,t2.NAME)  name  ,t2.name as sqname,case when t3.name='街道' then '开福区' else concat(ifnull(t4.name,''),t3.name) end  as parentName,t2.parentId,t1.jwd from  ilrv7_jwdsj t1 left join h_org_department t2 \n" + " on t1.bm = t2.id  \n" + "  left join h_org_department t3 on t2.parentId = t3.id  \n" + "  left join h_org_department t4 on t3.parentId = t4.id  and t4.id <> '2c9a8e6488e8fca60188e912ee421f97'  \n" + "where t3.parentId = '%s'", parentId);
        log.info("sql = :{}", finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }

    @GetMapping("/getFxyh")
    public List<Map<String, Object>> getFxyh(@RequestParam("startDate") String startDate, @RequestParam("endDate") String endDate, @RequestParam("deptId") String deptId, @RequestParam("shaoType") String shaoType) {
        if (StringUtils.isBlank(startDate)) {
            startDate = "1900-01-01 00:00:00";
        }
        if (StringUtils.isBlank(endDate)) {
            endDate = "2099-01-01 00:00:00";
        }
        if (StringUtils.isEmpty(deptId)) {
            deptId = "2c9a8e6488e8fca60188e912ee421f97";
        }
        String finalQuerySql = "";
        if (deptId.equals("2c9a8e6488e8fca60188e912ee421f97")) {
            finalQuerySql = String.format(" select c.id,c.sequenceNo,wi.id as workflowInstanceId ,\n" + " replace(JSON_EXTRACT(c.Address1685712261662, '$.lng') ,'\"','')as lng,\n" + " replace(JSON_EXTRACT(c.Address1685712261662, '$.lat') ,'\"','')as lat \n" + " from ikhmj_chuishao_001 c inner join biz_workflow_instance wi \n" + " on c.workflowInstanceId = wi.id \n" + "left join h_org_department d on c.createdDeptId = d.id and d.deleted = 0 \n" + " where  ( c.eventStatus != '删除' or  c.eventStatus is null ) and c.sequenceStatus  ='PROCESSING' \n" + "and c.createdTime BETWEEN '%s' and  '%s'    \n", startDate, endDate);

        } else {
            finalQuerySql = String.format(" select c.id,c.sequenceNo,wi.id as workflowInstanceId ,\n" + " replace(JSON_EXTRACT(c.Address1685712261662, '$.lng') ,'\"','')as lng,\n" + " replace(JSON_EXTRACT(c.Address1685712261662, '$.lat') ,'\"','')as lat \n" + " from ikhmj_chuishao_001 c inner join biz_workflow_instance wi \n" + " on c.workflowInstanceId = wi.id \n" + "left join h_org_department d on c.createdDeptId = d.id and d.deleted = 0 \n" + " where  ( c.eventStatus != '删除' or  c.eventStatus is null ) and c.sequenceStatus  ='PROCESSING' \n" + "and c.createdTime BETWEEN '%s' and  '%s'    \n" + "and d.queryCode like (select concat(queryCode,'%s') from h_org_department where id = '%s') \n", startDate, endDate, "%", deptId);
        }
        if(shaoType.equals("群众哨")){
            finalQuerySql += " and Radio1698973951943 ='是' ";
        }else if(shaoType.equals("区级哨")){
            finalQuerySql += " and eventStatus = '吹哨' ";
        }else{
             finalQuerySql += " and (Radio1698973951943 ='是' or eventStatus = '吹哨') ";
        }
        log.info("sql = :{}", finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }



     @GetMapping("/getYBYH")
    public List<Map<String, Object>> getYBYH(@RequestParam("deptId") String deptId,@RequestParam("type") String type,@RequestParam("yhfl") String yhfl) {
        // if (StringUtils.isBlank(startDate)) {
        //     startDate = "1900-01-01 00:00:00";
        // }
        // if (StringUtils.isBlank(endDate)) {
        //     endDate = "2099-01-01 00:00:00";
        // }
        if (StringUtils.isEmpty(deptId)) {
            deptId = "2c9a8e6488e8fca60188e912ee421f97";
        }
        if (StringUtils.isEmpty(type)) {
            type = "1";
        }
         if (StringUtils.isEmpty(yhfl)) {
            yhfl = "全部";
        }
        String finalQuerySql = "";
        if (deptId.equals("2c9a8e6488e8fca60188e912ee421f97")) {
            finalQuerySql = String.format("SELECT 'ybyh' as type, c.id,c.sequenceNo,\n" + 
            " REPLACE (JSON_EXTRACT( c.YhAddress, '$.lng' ),'\"','' ) AS lng,REPLACE (JSON_EXTRACT( c.YhAddress, '$.lat' ),'\"','' ) AS lat  \n" + 
            "	,	REPLACE (JSON_EXTRACT( c.YhAddress, '$.address' ),'\"','' ) AS address  \n" +
            ", hyfl,case when LENGTH(mainIssues)<=75 then mainIssues else concat(LEFT(mainIssues,25),'....') end as mainIssues,hazardName ,investigationDate \n" +
            " FROM ii2yk_overallandsignificant c 	LEFT JOIN h_org_department d ON c.DeptSingle1704098870868 = d.id  AND d.deleted = 0  \n" + 
            " WHERE c.sequenceStatus = 'COMPLETED'    \n"
            );
        } else {
            finalQuerySql =String.format("SELECT 'ybyh' as type,  c.id,c.sequenceNo,\n" + 
            " REPLACE (JSON_EXTRACT( c.YhAddress, '$.lng' ),'\"','' ) AS lng,REPLACE (JSON_EXTRACT( c.YhAddress, '$.lat' ),'\"','' ) AS lat  \n" + 
            "	,	REPLACE (JSON_EXTRACT( c.YhAddress, '$.address' ),'\"','' ) AS address  \n" +
	        ", hyfl,case when LENGTH(mainIssues)<=75 then mainIssues else concat(LEFT(mainIssues,25),'....') end as mainIssues,hazardName ,investigationDate \n" +
            " FROM ii2yk_overallandsignificant c 	LEFT JOIN h_org_department d ON c.DeptSingle1704098870868 = d.id  AND d.deleted = 0  \n" + 
            " WHERE c.sequenceStatus = 'COMPLETED'    \n" +
            " and d.queryCode like (select concat(queryCode,'%s') from h_org_department where id = '%s') \n", "%", deptId);
        }
        if(!"全部".equals(yhfl)){
                String[] values =  yhfl.split(",");
                finalQuerySql += " and hyfl in (" ;
                for (int i = 0; i < values.length; i++) {
                    if (i > 0) {
                        finalQuerySql += ", ";
                    }
                    finalQuerySql += "'" + values[i] + "'";
                }
                finalQuerySql += ")";
        }
        log.info("sql = :{}", finalQuerySql);
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }


     /*
        群众哨事件数量统计（数量统计）getQzEventCount
        传入参数  startDate ,endDate ,deptId
        返回参数List   总事件、未办结、已办结、办结率，满意率
        total,completedCnt,processingCnt,completionRate,satisfactionRate
    */
    @GetMapping("/getQzEventCount")
    public List<Map<String, Object>> getQzEventCount(@RequestParam("startDate") String startDate, @RequestParam("endDate") String endDate, @RequestParam("deptId") String deptId) {
        log.info("getQzEventCount传入startDate :{}", startDate);
        log.info("getQzEventCount传入endDate :{}", endDate);
        log.info("getQzEventCount传入deptId :{}", deptId);
        if (StringUtils.isBlank(startDate)) {
            startDate = "1900-01-01 00:00:00";
        }
        if (StringUtils.isBlank(endDate)) {
            endDate = "2099-01-01 00:00:00";
        }
        if (StringUtils.isBlank(deptId)) {
            deptId = "2c9a8e6488e8fca60188e912ee421f97";
        }
        String finalQuerySql = "";
        if (deptId.equals("2c9a8e6488e8fca60188e912ee421f97")) {
            finalQuerySql = String.format("SELECT completedCnt+processingCnt AS total,completedCnt,processingCnt,qjs,rcs, \n" + "100-ifnull (round(processingCnt/(completedCnt+processingCnt)*100,2),0) as completionRate,\n" + "100-ifnull (round(dissatisfactionCnt/cntc*100,2),0) as satisfactionRate FROM ( \n" + "SELECT sum(case when sequenceStatus='COMPLETED' then 1 else 0  end)  AS completedCnt,  \n" + "sum(case when sequenceStatus='PROCESSING' then 1 else 0  end)  processingCnt,\n" +
            " 	 	sum( case when eventStatus = '吹哨' then 1 else 0 end) as qjs,sum( case when eventStatus <> '吹哨' or eventStatus is null  then 1 else 0 end) as rcs,\n"
            + "sum(CASE WHEN radio1685887886485='不满意' OR myd_shifoumanyi='不满意' THEN 1 ELSE 0 END) AS dissatisfactionCnt, \n" + "sum(CASE WHEN wi.sequenceNo IS NOT NULL THEN 1 ELSE 0 END) AS cntc    \n" + "FROM ikhmj_chuishao_001 c \n" + "left join h_org_department t2 on c.createdDeptId = t2.id and t2.deleted =0 \n" + "LEFT JOIN (SELECT sequenceNo FROM biz_workitem_finished WHERE workflowCode='ce_001' \n" + "AND activityCode IN ('Activity8','Activity9') GROUP BY sequenceNo) wi ON wi.sequenceNo=c.sequenceNo \n" + "WHERE  c.eventStatus = '普通' \n" + "and c.sequenceStatus in('PROCESSING','COMPLETED')  \n" + "and c.createdTime BETWEEN '%s' and  '%s' and Radio1698973951943 ='是'   )a \n", startDate, endDate);
            log.info("sql = :{}", finalQuerySql);
        } else {
            finalQuerySql = String.format("SELECT completedCnt+processingCnt AS total,completedCnt,processingCnt,qjs,rcs, \n" + "100-ifnull (round(processingCnt/(completedCnt+processingCnt)*100,2),0) as completionRate,\n" + "100-ifnull (round(dissatisfactionCnt/cntc*100,2),0) as satisfactionRate FROM ( \n" + "SELECT sum(case when sequenceStatus='COMPLETED' then 1 else 0  end)  AS completedCnt,  \n" + "sum(case when sequenceStatus='PROCESSING' then 1 else 0  end)  processingCnt,\n" +
            " sum( case when eventStatus = '吹哨' then 1 else 0 end) as qjs,sum( case when eventStatus <> '吹哨' or eventStatus is null  then 1 else 0 end) as rcs,\n"
            + "sum(CASE WHEN radio1685887886485='不满意' OR myd_shifoumanyi='不满意' THEN 1 ELSE 0 END) AS dissatisfactionCnt, \n" + "sum(CASE WHEN wi.sequenceNo IS NOT NULL THEN 1 ELSE 0 END) AS cntc    \n" + "FROM ikhmj_chuishao_001 c \n" + "left join h_org_department t2 on c.createdDeptId = t2.id and t2.deleted =0 \n" + "LEFT JOIN (SELECT sequenceNo FROM biz_workitem_finished WHERE workflowCode='ce_001' \n" + "AND activityCode IN ('Activity8','Activity9') GROUP BY sequenceNo) wi ON wi.sequenceNo=c.sequenceNo \n" + "WHERE  c.eventStatus = '普通' \n" + "and c.sequenceStatus in('PROCESSING','COMPLETED')  \n" + "and c.createdTime BETWEEN '%s' and  '%s'    \n" + "and t2.queryCode like (select concat(queryCode,'%s') from h_org_department where id = '%s') and Radio1698973951943 ='是'  ) a ", startDate, endDate, "%", deptId);
            log.info("sql = :{}", finalQuerySql);
        }
        return this.jpaNativeQuery.getList(finalQuerySql, null, null);
    }

    /**
     * 社区居民情况
     */
    @GetMapping("/getCommunityResidentsCount")
    public Map getCommunityResidentsCount(@RequestParam("parentId") String parentId){
        Map result =new HashMap();
       String querySql = String.format("SELECT\n" +
                "\td.id AS id,\n" +
                "\td.NAME AS name,\n" +
                "\tCOUNT( r.id ) AS totals \n" +
                "FROM\n" +
                "\th_org_department d\n" +
                "\tLEFT JOIN iknn4_resident r ON ( r.belongJd = d.id OR r.belongSq = d.id OR r.belongWg = d.id ) \n" +
                "WHERE\n" +
                "\td.parentId = '%s' \n" +
               "and r.sequenceStatus = 'COMPLETED'  AND ifnull(r.dataStatus, '' ) <> '废弃'"+
                "\tAND d.id IN (\n" +
                "\tSELECT\n" +
                "\t\tt2.id \n" +
                "\tFROM\n" +
                "\t\tilrv7_jwdsj t1\n" +
                "\t\tLEFT JOIN h_org_department t2 ON t1.bm = t2.id\n" +
                "\t\tLEFT JOIN h_org_department t3 ON t2.parentId = t3.id\n" +
                "\t\tLEFT JOIN h_org_department t4 ON t3.parentId = t4.id \n" +
                "\t\tAND t4.id <> '2c9a8e6488e8fca60188e912ee421f97' \n" +
                "\tWHERE\n" +
                "\t\tt2.parentId = '%s'\n" +
                ") \n" +
                "GROUP BY\n" +
                "\td.id,\n" +
                "\td.NAME \n" +
                "ORDER BY\n" +
                "\ttotals desc;",parentId ,parentId);
       String queryParentId =String.format("select id from h_org_department where parentId = '%s'",parentId);
        List list1 = jpaNativeQuery.getList(queryParentId, null, null);
        if (CollectionUtils.isEmpty(list1)){
           querySql= querySql.replaceAll("d.parentId", "d.id").replaceAll("t2.parentId","t2.id");
        }
        List<Map<String,Object>> list = this.jpaNativeQuery.getList(querySql, null, null);
        if (CollectionUtils.isNotEmpty(list)){
            int count = list.stream()
                    .mapToInt(map -> {
                        BigInteger bigInteger = (BigInteger) map.get("totals");
                        if (bigInteger == null || bigInteger.compareTo(BigInteger.valueOf(Integer.MAX_VALUE)) > 0) {
                            throw new IllegalArgumentException("BigInteger value is too large to fit in an int");
                        }
                        return bigInteger.intValueExact();
                    })
                    .sum();
            result.put("total",list);
            //总数要重新统计
            String countSql = "";
            if (parentId.equals("2c9a8e6488e8fca60188e912ee421f97")){
                countSql = "SELECT\n" +
                        "\tcount(*) AS count \n" +
                        "FROM\n" +
                        "\tiknn4_resident t1 \n" +
                        "WHERE\n" +
                        "\tt1.sequenceStatus = 'COMPLETED' \n" +
                        "\tAND ifnull( t1.dataStatus, '' ) <> '废弃';";
            }else {
                countSql = String.format("select count(*) as count from iknn4_resident t1 LEFT JOIN h_org_department t2 on  t2.id = t1.ownerDeptQueryCode  where t2.id = '%s' and t1.sequenceStatus = 'COMPLETED'  AND ifnull(t1.dataStatus, '' ) <> '废弃';",parentId);
                String querydep = "select name from h_org_department where id = '"+parentId+"'";
                List<Map<String,Object>> list2 = jpaNativeQuery.getList(querydep, null, null);
                Map<String, Object> objectMap = list2.get(0);
                String depname =(String) objectMap.get("name");
                    if (depname.contains("街道")){
                        countSql = countSql.replaceAll("t1.ownerDeptQueryCode", "t1.belongJd");
                    }
                    if (depname.contains("社区")||depname.contains("村")){
                        countSql =countSql.replaceAll("t1.ownerDeptQueryCode", "t1.belongSq");
                    }
                    if (depname.contains("网格")){
                        countSql =countSql.replaceAll("t1.ownerDeptQueryCode", "t1.affiliationDept");
                    }
                }
            List<Map<String,Object>> counList = jpaNativeQuery.getList(countSql, null, null);
            result.put("count",counList.get(0).get("count"));
            return result;
        }
        return result;
    }

    /**
     * 网格力量情况
     */
    @GetMapping("getGridPowerSituation")
    public Map<String,Object> getGridPowerSituation(@RequestParam("parentId") String parentId){
        Map result =new HashMap();
        String querySql = String.format("SELECT\n" +
                "\td.id AS id,\n" +
                "\td.NAME AS name,\n" +
                "\tCOUNT( r.id ) AS totals \n" +
                "FROM\n" +
                "\th_org_department d\n" +
                "\tLEFT JOIN ikhmj_txl r ON ( r.jd = d.id OR r.sq = d.id OR r.wg = d.id ) \n" +
                "WHERE\n" +
                "\td.parentId = '%s' \n" +
                "\tAND d.id IN (\n" +
                "\tSELECT\n" +
                "\t\tt2.id \n" +
                "\tFROM\n" +
                "\t\tilrv7_jwdsj t1\n" +
                "\t\tLEFT JOIN h_org_department t2 ON t1.bm = t2.id\n" +
                "\t\tLEFT JOIN h_org_department t3 ON t2.parentId = t3.id\n" +
                "\t\tLEFT JOIN h_org_department t4 ON t3.parentId = t4.id \n" +
                "\t\tAND t4.id <> '2c9a8e6488e8fca60188e912ee421f97' \n" +
                "\tWHERE\n" +
                "\t\tt2.parentId = '%s'\n" +
                ") \n" +
                "GROUP BY\n" +
                "\td.id,\n" +
                "\td.NAME \n" +
                "ORDER BY\n" +
                "\ttotals desc;",parentId,parentId);
        String queryParentId =String.format("select id from h_org_department where parentId = '%s'",parentId);
        List list1 = jpaNativeQuery.getList(queryParentId, null, null);
        if (CollectionUtils.isEmpty(list1)){
            querySql= querySql.replaceAll("d.parentId", "d.id").replaceAll("t2.parentId","t2.id");
        }
        String querydep = "select name from h_org_department where id = '"+parentId+"'";
        List<Map<String,Object>> list2 = jpaNativeQuery.getList(querydep, null, null);
        Map<String, Object> objectMap = list2.get(0);
        String depname =(String) objectMap.get("name");
        List<Map<String,Object>> list = this.jpaNativeQuery.getList(querySql, null, null);
        if (CollectionUtils.isNotEmpty(list)){
            int count = list.stream()
                    .mapToInt(map -> {
                        BigInteger bigInteger = (BigInteger) map.get("totals");
                        if (bigInteger == null || bigInteger.compareTo(BigInteger.valueOf(Integer.MAX_VALUE)) > 0) {
                            throw new IllegalArgumentException("BigInteger value is too large to fit in an int");
                        }
                        return bigInteger.intValueExact();
                    })
                    .sum();
            result.put("total",list);
            String countSql = String.format("select count(*) as count from ikhmj_txl t1 LEFT JOIN h_org_department t2 on t2.id = t1.createdDeptId where t2.id = '%s'",parentId);
            if (depname.contains("街道")){
                countSql = countSql.replaceAll("t2.id = t1.createdDeptId", "t2.id = t1.jd");
            }
            if (depname.contains("社区")||depname.contains("村")){
                countSql =countSql.replaceAll("t2.id = t1.createdDeptId", "t2.id =t1.sq");
            }
            if (depname.contains("网格")){
                countSql =countSql.replaceAll("t2.id = t1.createdDeptId", "t2.id =t1.wg");
            }
            if (parentId.equals("2c9a8e6488e8fca60188e912ee421f97")){
                countSql = "select count(*) as count from ikhmj_txl t1";
            }
            List<Map<String,Object>> counList = jpaNativeQuery.getList(countSql, null, null);
            result.put("count",counList.get(0).get("count"));
            return result;
        }
        return result;
    }

    /**
     * 五类人群情况
     */
    @GetMapping("/getFiveTypesAll")
    public Map<String,BigDecimal> getFiveTypesAll(@RequestParam("parentId") String parentId){
        String queyrSql = String.format("SELECT COALESCE\n" +
                "\t( SUM( CASE WHEN h.personnelNature LIKE '%%户籍人员%%' THEN 1 ELSE 0 END ), 0 ) AS \"户籍人员\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%流动人口%%' THEN 1 ELSE 0 END ), 0 ) AS \"流动人口\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%党员%%' THEN 1 ELSE 0 END ), 0 ) AS \"党员\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%普通居民%%' THEN 1 ELSE 0 END ), 0 ) AS \"普通居民\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%困境儿童%%' THEN 1 ELSE 0 END ), 0 ) AS \"困境儿童\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%低保户%%' THEN 1 ELSE 0 END ), 0 ) AS \"低保户\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%残疾人%%' THEN 1 ELSE 0 END ), 0 ) AS \"残疾人\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%80岁高龄老人%%' THEN 1 ELSE 0 END ), 0 ) AS \"80岁高龄老人\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%空巢、失能失智老人%%' THEN 1 ELSE 0 END ), 0 ) AS \"空巢、失能失智老人\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%矫正对象%%' THEN 1 ELSE 0 END ), 0 ) AS \"矫正对象\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%涉毒人员%%' THEN 1 ELSE 0 END ), 0 ) AS \"涉毒人员\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%维稳对象%%' THEN 1 ELSE 0 END ), 0 ) AS \"维稳对象\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%特困人员%%' THEN 1 ELSE 0 END ), 0 ) AS \"特困人员\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%退役军人%%' THEN 1 ELSE 0 END ), 0 ) AS \"退役军人\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%现役军人%%' THEN 1 ELSE 0 END ), 0 ) AS \"现役军人\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%610人员%%' THEN 1 ELSE 0 END ), 0 ) AS \"610人员\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%特扶人员%%' THEN 1 ELSE 0 END ), 0 ) AS \"特扶人员\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%租户%%' THEN 1 ELSE 0 END ), 0 ) AS \"租户\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%已过世人员%%' THEN 1 ELSE 0 END ), 0 ) AS \"已过世人员\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%散户%%' THEN 1 ELSE 0 END ), 0 ) AS \"散户\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%刑满释放%%' THEN 1 ELSE 0 END ), 0 ) AS \"刑满释放\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%监狱人员%%' THEN 1 ELSE 0 END ), 0 ) AS \"监狱人员\",\n" +
                "\tCOALESCE ( SUM( CASE WHEN h.personnelNature LIKE '%%统战人士%%' THEN 1 ELSE 0 END ), 0 ) AS \"统战人士\" \n" +
                "FROM\n" +
                "\tiknn4_resident h\n" +
                "\tLEFT JOIN h_org_department d ON ( h.belongJd = d.id OR h.belongSq = d.id OR h.affiliationDept = d.id ) \n" +
                "WHERE\n" +
                "\th.sequenceStatus = 'COMPLETED' \n" +
                "\tAND ifnull( h.dataStatus, '' ) <> '废弃' ");
        if (parentId.equals("2c9a8e6488e8fca60188e912ee421f97")) {
            queyrSql = queyrSql +"and d.parentId = '2c9a8e6488e8fca60188e912ee421f97'";
        }else {
            queyrSql = queyrSql +"AND d.id = '"+parentId+"'";
        }
        log.info("getFiveTypes---SQL:{}",queyrSql);
        List<Map<String, BigDecimal>> list = this.jpaNativeQuery.getList(queyrSql,null,null);
        LinkedHashMap<String, BigDecimal> collect = list.get(0).entrySet().stream()
                .sorted(Map.Entry.comparingByValue(Comparator.reverseOrder()))
                .collect(Collectors.toMap(
                        Map.Entry::getKey,
                        Map.Entry::getValue,
                        (oldValue, newValue) -> oldValue,
                        LinkedHashMap::new));
        // 创建一个包含特定键的列表，用于优先排序
        List<String> priorityKeys = Arrays.asList("户籍人员", "流动人口", "党员");

        // 使用自定义比较器对Map的entrySet进行排序
        List<Map.Entry<String, BigDecimal>> sortedEntries = new ArrayList<>(collect.entrySet());
        sortedEntries.sort((o1, o2) -> {
            // 首先检查键是否在优先列表中
            int p1 = priorityKeys.indexOf(o1.getKey());
            int p2 = priorityKeys.indexOf(o2.getKey());

            // 如果都在优先列表中，或者都不在，则按照值的大小排序
            if (p1 != -1 && p2 != -1) {
                return Integer.compare(p1, p2); // 优先列表中的顺序
            } else if (p1 == -1 && p2 == -1) {
                return o2.getValue().compareTo(o1.getValue()); // 其他键按值倒序
            }

            // 如果一个在优先列表中，一个不在，则优先列表中的在前
            return p1 != -1 ? -1 : 1;
        });

        // 创建一个新的LinkedHashMap来保存排序后的结果
        Map<String, BigDecimal> sortedMap = new LinkedHashMap<>();
        for (Map.Entry<String, BigDecimal> entry : sortedEntries) {
            sortedMap.put(entry.getKey(), entry.getValue());
        }
        return sortedMap;

    }

    /**
     * 五类人群情况
     */
    @GetMapping("/getFiveTypes")
    public Map<String,BigDecimal> getFiveTypes(@RequestParam("parentId") String parentId){
        String queyrSql = "SELECT " +
                "COALESCE(SUM(CASE WHEN h.personnelNature LIKE '%户籍人员%' THEN 1 ELSE 0 END), 0) AS 户籍人员, " +
                "COALESCE(SUM(CASE WHEN h.personnelNature LIKE '%流动人口%' THEN 1 ELSE 0 END), 0) AS 流动人口, " +
                "COALESCE(SUM(CASE WHEN h.personnelNature LIKE '%党员%' THEN 1 ELSE 0 END), 0) AS 党员 " +
                "FROM iknn4_resident h " +
                "LEFT JOIN h_org_department d " +
                "ON h.belongJd = d.id OR h.belongSq = d.id OR h.affiliationDept = d.id " +
                "WHERE h.sequenceStatus = 'COMPLETED' " +
                "AND IFNULL(h.dataStatus, '') <> '废弃'";
        if (parentId.equals("2c9a8e6488e8fca60188e912ee421f97")) {
            queyrSql = queyrSql +"and d.parentId = '2c9a8e6488e8fca60188e912ee421f97'";
        }else {
            queyrSql = queyrSql +"AND d.id = '"+parentId+"'";
        }
        log.info("getFiveTypes---SQL:{}",queyrSql);
        List<Map<String, BigDecimal>> list = this.jpaNativeQuery.getList(queyrSql,null,null);
        LinkedHashMap<String, BigDecimal> collect = list.get(0).entrySet().stream()
                .sorted(Map.Entry.comparingByValue(Comparator.reverseOrder()))
                .collect(Collectors.toMap(
                        Map.Entry::getKey,
                        Map.Entry::getValue,
                        (oldValue, newValue) -> oldValue,
                        LinkedHashMap::new));
        // 定义你想要的键的顺序
        List<String> keyOrder = Arrays.asList("户籍人员", "流动人口", "党员");

        // 创建一个新的 LinkedHashMap 来存放排序后的键值对
        LinkedHashMap<String, BigDecimal> sortedMap = new LinkedHashMap<>();

        // 遍历 keyOrder 列表，并从 originalMap 中获取对应的值
        for (String key : keyOrder) {
            if (collect.containsKey(key)) {
                sortedMap.put(key, collect.get(key));
            }
        }

        return sortedMap;

    }

    /**
     * 基础数据情况
     */
    @GetMapping("/getBasicData")
    public List<Map<String,Object>> getTalentStructure(@RequestParam("parentId") String parentId){
        String querySql = String.format("SELECT\n" +
                "\t'fwzs' AS \"type\",\n" +
                "\tcount( t1.id ) AS \"count\" \n" +
                "FROM\n" +
                "\tiknn4_house t1\n" +
                "\tLEFT JOIN h_org_department dep ON dep.id = t1.createdDeptId \n" +
                "WHERE\n" +
                "\tt1.sequenceStatus = 'COMPLETED' \n" +
                "\tAND ifnull( t1.dataStatus, '' ) <> '废弃' \n" +
                "\tAND dep.id = '%s' and t1.creater != '2c9280a26706a73a016706a93ccf002b' UNION ALL\n" +
                "SELECT\n" +
                "\t'sqzs' AS \"type\",\n" +
                "\tcount( t1.id ) AS \"count\" \n" +
                "FROM\n" +
                "\t`iknn4_business` t1\n" +
                "\tLEFT JOIN h_org_department dep ON dep.id = t1.createdDeptId \n" +
                "WHERE\n" +
                "\tt1.sequenceStatus = 'COMPLETED' \n" +
                "\tAND ifnull( t1.dataStatus, '' ) <> '废弃' \n" +
                "\tAND dep.id = '%s' and t1.creater != '2c9280a26706a73a016706a93ccf002b' UNION ALL\n" +
                "SELECT\n" +
                "\t'xxzs' AS \"type\",\n" +
                "\tcount(*) AS \"count\" \n" +
                "FROM\n" +
                "\t`iknn4_school` t1\n" +
                "\tLEFT JOIN h_org_department dep ON dep.id = t1.createdDeptId \n" +
                "WHERE\n" +
                "\tt1.sequenceStatus = 'COMPLETED' \n" +
                "\tAND ifnull( t1.dataStatus, '' ) <> '废弃' \n" +
                "\tAND dep.id = '%s' and t1.creater != '2c9280a26706a73a016706a93ccf002b' UNION ALL\n" +
                "SELECT\n" +
                "\t'jmzs' AS \"type\",\n" +
                "\tcount(*) AS \"count\" \n" +
                "FROM\n" +
                "\tiknn4_resident t1\n" +
                "\tLEFT JOIN h_org_department dep ON dep.id = t1.createdDeptId \n" +
                "WHERE\n" +
                "\tt1.sequenceStatus = 'COMPLETED' \n" +
                "\tAND ifnull( t1.dataStatus, '' ) <> '废弃' \n" +
                "\tAND dep.id = '%s' and t1.creater != '2c9280a26706a73a016706a93ccf002b';",parentId,parentId,parentId,parentId);
        if (parentId.equals("2c9a8e6488e8fca60188e912ee421f97")){
            querySql = "SELECT\n" +
                    "\t'fwzs' AS \"type\",\n" +
                    "\tcount( h.id ) AS \"count\" \n" +
                    "FROM\n" +
                    "\t`iknn4_house` h \n" +
                    "WHERE\n" +
                    "\th.sequenceStatus = 'COMPLETED' \n" +
                    "\tAND ifnull( h.dataStatus, '' ) <> '废弃' UNION ALL\n" +
                    "SELECT\n" +
                    "\t'sqzs' AS \"type\",\n" +
                    "\tcount( b.id ) AS \"count\" \n" +
                    "FROM\n" +
                    "\t`iknn4_business` b \n" +
                    "WHERE\n" +
                    "\tb.sequenceStatus = 'COMPLETED' \n" +
                    "\tAND ifnull( b.dataStatus, '' ) <> '废弃' UNION ALL\n" +
                    "SELECT\n" +
                    "\t'xxzs' AS \"type\",\n" +
                    "\tcount(*) AS \"count\" \n" +
                    "FROM\n" +
                    "\t`iknn4_school` s \n" +
                    "WHERE\n" +
                    "\ts.sequenceStatus = 'COMPLETED' \n" +
                    "\tAND ifnull( s.dataStatus, '' ) <> '废弃' UNION ALL\n" +
                    "SELECT\n" +
                    "\t'jmzs' AS \"type\",\n" +
                    "\tcount(*) AS \"count\" \n" +
                    "FROM\n" +
                    "\t`iknn4_resident` r \n" +
                    "WHERE\n" +
                    "\tr.sequenceStatus = 'COMPLETED' \n" +
                    "and t1.creater <> '2c9280a26706a73a016706a93ccf002b'"+
                    "\tAND ifnull( r.dataStatus, '' ) <> '废弃';";
        }else {
            String querydep = "select name from h_org_department where id = '"+parentId+"'";
            List<Map<String,Object>> list = jpaNativeQuery.getList(querydep, null, null);
            Map<String, Object> objectMap = list.get(0);
            String depname =(String) objectMap.get("name");
            if (depname.contains("街道")){
                querySql = querySql.replaceAll("t1.createdDeptId", "t1.belongJd");
            }
            if (depname.contains("社区")||depname.contains("村")){
                querySql =querySql.replaceAll("t1.createdDeptId", "t1.belongSq");
            }
            if (depname.contains("网格")){
                querySql =querySql.replaceAll("t1.createdDeptId", "t1.belongWg");
                querySql =querySql.replaceAll("iknn4_house t1 LEFT JOIN h_org_department dep ON dep.id = t1.belongWg","iknn4_house t1 LEFT JOIN h_org_department dep ON dep.id = t1.affiliationDept");
                querySql = querySql.replaceAll("iknn4_resident t1 LEFT JOIN h_org_department dep ON dep.id = t1..belongWg","`iknn4_resident t1 LEFT JOIN h_org_department dep ON dep.id = t1.affiliationDept");
            }
        }
        log.info("SQL语句:{}",querySql);
        return this.jpaNativeQuery.getList(querySql,null,null);
    }

    /**
     * 五零走访日志情况
     *
     * @return
     */
    @GetMapping("/getFiveZeroLog")
    public List<Map<String,Object>> getFiveZeroLog(@RequestParam("deptId") String deptId,@RequestParam(value = "startDate",required = false) String startDate,@RequestParam(value = "endDate",required = false) String endDate){
        if (StringUtils.isBlank(startDate)) {
            startDate = "1900-01-01 00:00:00";
        }
        if (StringUtils.isBlank(endDate)) {
            endDate = "2099-01-01 00:00:00";
        }
        String querySql = "";
        if (deptId.equals("2c9a8e6488e8fca60188e912ee421f97")){
            querySql = String.format("\tSELECT\n" +
                    "\tcount( c.id ) AS total,\n" +
                    "\tsum( CASE WHEN c.theme = '安全零事故' THEN 1 ELSE 0 END ) AS aqCnt,\n" +
                    "\tsum( CASE WHEN c.theme = '服务零距离' THEN 1 ELSE 0 END ) AS fwCnt,\n" +
                    "\tsum( CASE WHEN c.theme = '环境零污染' THEN 1 ELSE 0 END ) AS hjCnt,\n" +
                    "\tsum( CASE WHEN c.theme = '治安零发案' THEN 1 ELSE 0 END ) AS zaCnt,\n" +
                    "\tsum( CASE WHEN c.theme = '居民零上访' THEN 1 ELSE 0 END ) AS jmCnt \n" +
                    "FROM\n" +
                    "\tiknn4_interviewworklog c\n" +
                    "WHERE\n" +
                    "\tc.sequenceStatus != 'DRAFT' and c.createdTime BETWEEN '%s' and  '%s'  ",startDate,endDate);
        }else {
            querySql = String.format("SELECT\n" +
                    "\tcount( c.id ) AS total,\n" +
                    "\tsum( CASE WHEN c.theme = '安全零事故' THEN 1 ELSE 0 END ) AS aqCnt,\n" +
                    "\tsum( CASE WHEN c.theme = '服务零距离' THEN 1 ELSE 0 END ) AS fwCnt,\n" +
                    "\tsum( CASE WHEN c.theme = '环境零污染' THEN 1 ELSE 0 END ) AS hjCnt,\n" +
                    "\tsum( CASE WHEN c.theme = '治安零发案' THEN 1 ELSE 0 END ) AS zaCnt,\n" +
                    "\tsum( CASE WHEN c.theme = '居民零上访' THEN 1 ELSE 0 END ) AS jmCnt \n" +
                    "FROM\n" +
                    "\tiknn4_interviewworklog c\n" +
                    "\tLEFT JOIN h_org_department d on  d.id = c.createdDeptId\n" +
                    "WHERE\n" +
                    "\tc.sequenceStatus != 'DRAFT' \n" +
                    "\tAND d.queryCode LIKE ( SELECT concat( queryCode, '%%' ) FROM h_org_department WHERE id = '%s' ) and c.createdTime BETWEEN '%s' and  '%s' ",deptId,startDate,endDate);
            String querydep = "select name from h_org_department where id = '"+deptId+"'";
            List<Map<String,Object>> list = jpaNativeQuery.getList(querydep, null, null);
            Map<String, Object> objectMap = list.get(0);
            String depname =(String) objectMap.get("name");
            if (depname.contains("街道")){
                querySql = querySql.replaceAll("c.createdDeptId", "c.belongJd");
            }
            if (depname.contains("社区")||depname.contains("村")){
                querySql =querySql.replaceAll("c.createdDeptId", "c.belongSq");
            }
            if (depname.contains("网格")){
                querySql =querySql.replaceAll("c.createdDeptId", "c.belongWg");
            }
        }
        log.info("getFiveZerLog-----SQL:{}",querySql);
        return jpaNativeQuery.getList(querySql,null,null);
    }
    /**
     * 走访发现问题
     */
    @GetMapping("/getVisitsIssue")
    public List<Map<String,Object>>  getVisitsIssue(@RequestParam("deptId") String deptId,@RequestParam(value = "startDate",required = false) String startDate,@RequestParam(value = "endDate",required = false) String endDate){
        if (StringUtils.isBlank(startDate)) {
            startDate = "1900-01-01 00:00:00";
        }
        if (StringUtils.isBlank(endDate)) {
            endDate = "2099-01-01 00:00:00";
        }
        String querySql = String.format("SELECT\n" +
                "\t( SELECT NAME FROM h_org_department WHERE id = ivl.belongSq ) AS sq,\n" +
                "\t( SELECT NAME FROM h_org_department WHERE id = ivl.belongWg ) AS wg,\n" +
                "\tivl.interviewObject,\n" +
                "\tivl.theme,\n" +
                "\tDate( ivl.interviewTime ) AS interviewTime \n" +
                "FROM\n" +
                "\t`iknn4_interviewworklog` ivl\n" +
                "\tLEFT JOIN h_org_department h ON ( ivl.belongJd = h.id OR ivl.belongSq = h.id OR ivl.belongWg = h.id )\n" +
                "\tLEFT JOIN h_org_user u ON ivl.OWNER = u.id \n" +
                "WHERE\n" +
                "\th.id = '%s' \n" +
                "\tAND ivl.sequenceStatus != 'DRAFT'\n" +
                "\tand ivl.createTime  BETWEEN '%s' and '%s'\n" +
                "ORDER BY\n" +
                "\tivl.interviewTime DESC",deptId,startDate,endDate);
        return jpaNativeQuery.getList(querySql,null,null);
    }

    /**
     * 吹哨情况统计
     */
    @GetMapping("/getSituationCount")
    public List<Map<String,Object>> getSituationCount(@RequestParam("depId") String depId,@RequestParam(value = "startDate",required = false) String startDate,@RequestParam(value = "endDate",required = false) String endDate){
        if (StringUtils.isBlank(startDate)) {
            startDate = "1900-01-01 00:00:00";
        }
        if (StringUtils.isBlank(endDate)) {
            endDate = "2099-01-01 00:00:00";
        }
        String querSql = "";
        if (depId.equals("2c9a8e6488e8fca60188e912ee421f97")){
            querSql = String.format("SELECT total, sqzb, jdzb, qjbj, completedCnt, processingCnt, 100-IFNULL ( round( processingCnt /( total )* 100,\n" +
                    "\t\t2 \n" +
                    "\t\t),\n" +
                    "\t0 \n" +
                    ") AS completionRate \n" +
                    "FROM\n" +
                    "\t(\n" +
                    "\tSELECT\n" +
                    "\t\tsum( CASE WHEN sequenceStatus = 'COMPLETED' OR sequenceStatus = 'PROCESSING' THEN 1 ELSE 0 END ) AS total,\n" +
                    "\t\tsum( CASE WHEN (sq_shangbao in( '自办' ,'办结') or c.wgy_clfs in( '自办' ,'办结') )  and ifnull(jd_shangbao,'') <>'吹哨' and ifnull(eventStatus,'') != '吹哨'   and  sequenceStatus = 'COMPLETED' THEN 1 ELSE 0 END ) sqzb,\n" +
                    "\t\tsum( CASE WHEN jd_shangbao in( '自办' ,'站办所处理' )   and ifnull(eventStatus,'') != '吹哨'  and  sequenceStatus = 'COMPLETED' THEN 1 ELSE 0 END ) AS jdzb,\n" +
                    "\t\tsum( CASE WHEN eventStatus = '吹哨' AND sequenceStatus = 'COMPLETED' THEN 1 ELSE 0 END ) AS qjbj,\n" +
                    "\t\tsum( CASE WHEN sequenceStatus = 'COMPLETED' THEN 1 ELSE 0 END ) AS completedCnt,\n" +
                    "\t\tsum( CASE WHEN sequenceStatus = 'PROCESSING' THEN 1 ELSE 0 END ) AS processingCnt \n" +
                    "\tFROM\n" +
                    "\t\tikhmj_chuishao_001 c\n" +
                    "\tWHERE\n" +
                    "\t\t( c.eventStatus != '删除' OR c.eventStatus IS NULL ) \n" +
                    "\t\tAND c.sequenceStatus IN ( 'PROCESSING', 'COMPLETED' ) \n" +
                    "\t\tAND c.createdTime BETWEEN '%s' \n" +
                    "\t\tAND '%s' \n" +
                    "\t) a;",startDate,endDate);
        }else {
            querSql = String.format("SELECT total, sqzb, jdzb, qjbj,  completedCnt, processingCnt,100-IFNULL ( round( processingCnt /( total )* 100,\n" +
                    "\t\t2 \n" +
                    "\t\t),\n" +
                    "\t0 \n" +
                    "\t) AS completionRate from  (\n" +
                    "\tSELECT\n" +
                    "\t\tsum( CASE WHEN sequenceStatus = 'COMPLETED' OR sequenceStatus = 'PROCESSING' THEN 1 ELSE 0 END ) AS total,\n" +
                    "\t\tsum( CASE WHEN (sq_shangbao in( '自办' ,'办结') or c.wgy_clfs in( '自办' ,'办结') )  and ifnull(jd_shangbao,'') <>'吹哨' and ifnull(eventStatus,'') != '吹哨'   and  sequenceStatus = 'COMPLETED' THEN 1 ELSE 0 END ) sqzb,\n" +
                    "\t\tsum( CASE WHEN jd_shangbao in( '自办' ,'站办所处理' )   and ifnull(eventStatus,'') != '吹哨'  and  sequenceStatus = 'COMPLETED' THEN 1 ELSE 0 END ) AS jdzb,\n" +
                    "\t\tsum( CASE WHEN eventStatus = '吹哨'  and sequenceStatus = 'COMPLETED' THEN 1 ELSE 0 END ) AS qjbj,\n" +
                    "sum(case when sequenceStatus = 'COMPLETED' THEN 1 ELSE 0 END ) AS completedCnt,"+
                    "\t\tsum( CASE WHEN sequenceStatus = 'PROCESSING' THEN 1 ELSE 0 END ) AS processingCnt \n" +
                    "\tFROM\n" +
                    "\t\tikhmj_chuishao_001 c\n" +
                    "\t\tLEFT JOIN h_org_department t2 ON c.createdDeptId = t2.id \n" +
                    "\t\tAND t2.deleted = 0 \n" +
                    "\tWHERE\n" +
                    "\t\t( c.eventStatus != '删除' OR c.eventStatus IS NULL ) \n" +
                    "\t\tAND c.sequenceStatus IN ( 'PROCESSING', 'COMPLETED' ) \n" +
                    "\t\tAND t2.queryCode LIKE ( SELECT concat( queryCode, '%%' ) FROM h_org_department WHERE id = '%s' ) \n" +
                    "\t\tAND c.createdTime BETWEEN '%s' \n" +
                    "\t\tAND '%s' \n" +
                    "\t) a",depId,startDate,endDate);
            String querydep = "select name from h_org_department where id = '"+depId+"'";
            List<Map<String,Object>> list = jpaNativeQuery.getList(querydep, null, null);
            Map<String, Object> objectMap = list.get(0);
            String depname =(String) objectMap.get("name");
            if (depname.contains("街道")){
                querSql = querSql.replaceAll("c.createdDeptId", "c.DeptSingle1698920071463");
            }
            if (depname.contains("社区")||depname.contains("村")){
                querSql =querSql.replaceAll("c.createdDeptId", "c.DeptSingle1698920069239");
            }
            if (depname.contains("网格")){
                querSql =querSql.replaceAll("c.createdDeptId", "c.DeptSingle1698920067265");
            }
        }
        return jpaNativeQuery.getList(querSql,null,null);
    }


    /**
     * 根据用户判断地图显示
     * @param parentId
     * @return
     */
    @GetMapping("/getMapByUser")
    public List<Map<String, Object>> getsqData(@RequestParam("parentId") String parentId,@RequestParam("type") String type) {
        String jdId=null;
        String sqId=null;
        if (StringUtils.isEmpty(parentId)) {
            parentId = "2c9a8e6488e8fca60188e912ee421f97";
            String userId = getUserId();
            String querySql = String.format("WITH RECURSIVE dept_path AS (  \n" +
                    "    SELECT id, name, parentid, CAST(id AS CHAR(255)) AS path  \n" +
                    "    FROM h_org_department  \n" +
                    "    WHERE id IN (SELECT DISTINCT deptId FROM h_org_dept_user WHERE userId = '%s')  \n" +
                    "  \n" +
                    "    UNION ALL  \n" +
                    "  \n" +
                    "    SELECT d.id, d.name, d.parentid, CONCAT(dp.path, ' -> ', d.id)  \n" +
                    "    FROM dept_path dp  \n" +
                    "    JOIN h_org_department d ON dp.parentid = d.id  \n" +
                    ")  \n" +
                    "  \n" +
                    "SELECT DISTINCT id, name, path  \n" +
                    "FROM dept_path  \n" +
                    "ORDER BY LENGTH(path) DESC;",userId);
            List<Map<String,String>> listMap = jpaNativeQuery.getList(querySql, null, null);
            List<Map<String, String>> collect = listMap.stream()
                    .filter(map -> map.containsKey("name") && (
                            map.get("name").contains("街道") ||
                                    map.get("name").contains("社区")
                    ))
                    .filter(map -> map.get("name").length() > 2)
                    .collect(Collectors.toMap(
                            map -> map.get("name"),
                            Function.identity(),
                            (oldValue, newValue) -> oldValue,
                            LinkedHashMap::new
                    ))
                    .values()
                    .stream()
                    .collect(Collectors.toList());
            for(Map<String, String> map : collect){
                if (map.get("name").contains("社区")){
                    sqId = map.get("id");
                }
                if (map.get("name").contains("街道")){
                    jdId = map.get("id");
                }
            }
        }
        if (type.equals("jd")&& StringUtils.isNotEmpty(jdId)){
            parentId = jdId;
        }
        if (type.equals("sq")&& StringUtils.isNotEmpty(sqId)){
            parentId = sqId;
        }
        String finalQuerySql = String.format("select  t1.jdid,t1.sqid,t2.id,t2.name ,case when t3.name='街道' then '开福区' else concat(ifnull(t4.name,''),t3.name) end  as parentName,t2.parentId,t1.jwd from  ilrv7_jwdsj t1 left join h_org_department t2 \n" + " on t1.bm = t2.id  \n" + "  left join h_org_department t3 on t2.parentId = t3.id  \n" + "  left join h_org_department t4 on t3.parentId = t4.id  and t4.id <> '2c9a8e6488e8fca60188e912ee421f97'  \n" + "where t2.parentId = '%s'", parentId);
        log.info("sql = :{}", finalQuerySql);
        List<Map<String,Object>> list = this.jpaNativeQuery.getList(finalQuerySql, null, null);
        if (CollectionUtils.isNotEmpty(list)){
            boolean containsCommunity = list.stream()
                    .map(map -> map.get("name"))
                    .filter(Objects::nonNull)
                    .map(Object::toString)
                    .anyMatch(name -> name.contains("社区"));
            if (containsCommunity){
                for(Map<String,Object> objectMap:list){
                    String queryGridSql = String.format("SELECT name FROM `h_org_department`  where parentId = '%s'",objectMap.get("id"));
                    List<Map<String,Object>> gridList = this.jpaNativeQuery.getList(queryGridSql,null,null);
                    String range = extractGridRange(gridList);
                    if(StringUtils.isNotBlank(range)){
                        String name =(String) objectMap.get("name");
                        objectMap.put("name",name+"("+range+")");
                    }
                }
            }
        }
        return list;
    }

    @GetMapping("/getUserDepId")
    public Result getUserDepId(@RequestParam("type") String type){
        String jdId="2c9a8e6488e8fca60188e912ee421f97";
        String sqId="2c9a8e6488e8fca60188e912ee421f97";
        String userId = getUserId();
        String querySql = String.format("WITH RECURSIVE dept_path(id, name, parentid, path) AS (  \n" +
                "    SELECT id, name, parentid, CAST(id AS CHAR(255)) AS path  \n" +
                "    FROM h_org_department  \n" +
                "    WHERE id IN (SELECT DISTINCT deptId FROM h_org_dept_user WHERE userId = '%s')  \n" +
                "  \n" +
                "    UNION ALL  \n" +
                "  \n" +
                "    SELECT d.id, d.name, d.parentid, CONCAT(dp.path, ' -> ', d.id)  \n" +
                "    FROM dept_path dp  \n" +
                "    JOIN h_org_department d ON dp.parentid = d.id  \n" +
                ")  \n" +
                "  \n" +
                "SELECT DISTINCT id, name, path  \n" +
                "FROM dept_path  \n" +
                "ORDER BY LENGTH(path) DESC;",userId);
        List<Map<String,String>> listMap = jpaNativeQuery.getList(querySql, null, null);
        List<Map<String, String>> collect = listMap.stream()
                .filter(map -> map.containsKey("name") && (
                        map.get("name").contains("街道") ||
                                map.get("name").contains("社区")||map.get("name").contains("村")
                ))
                .filter(map -> map.get("name").length() > 2)
                .collect(Collectors.toMap(
                        map -> map.get("name"),
                        Function.identity(),
                        (oldValue, newValue) -> oldValue,
                        LinkedHashMap::new
                ))
                .values()
                .stream()
                .collect(Collectors.toList());
        for(Map<String, String> map : collect){
            if (map.get("name").contains("社区")||map.get("name").contains("村")){
                sqId = map.get("id");
            }
            if (map.get("name").contains("街道")){
                jdId = map.get("id");
            }
        }
        return Result.ok(type.equals("jd")?jdId:sqId);
    }

    /**
     * 生产经营主体全覆盖
     * @param
     * @return
     */
    @GetMapping("/getFullCoverage")
    public List<Map<String,Object>> getFullCoverage(@RequestParam("depId") String depId) {
        String querUser = String.format("\tselect id,name from h_org_user");
        List<Map<String,Object>> userList = jpaNativeQuery.getList(querUser,null,null);
        String querySql = String.format("SELECT\n" +
                "\ts.id,\n" +
                "\t'securityInspection_test' as sheetCode,\n" +
                "\ts.belongSq,\n" +
                "\t(select name from h_org_department where id = s.belongSq) as sqName,\n" +
                "\ts.belongWg,\n" +
                "\t(select name from h_org_department where id = s.belongWg) as wgName,\n" +
                "\ts.relevanceBusiness as dw,\n" +
                "\t(select name from iknn4_business where id = s.relevanceBusiness) as bjcdw,\n" +
                "\tCAST(s.inspectionTime AS DATE) as inspectionTime,\n" +
                "\ts.inspectors,\n" +
                "\tinspectionType\n" +
                "FROM\n" +
                "\th_org_department d\n" +
                "\tLEFT JOIN iknn4_securityinspection_test s ON ( d.id = s.belongJd OR d.id = s.belongSq OR d.id = s.belongWg ) \n" +
                "WHERE\n" +
                "\td.id = '%s';\n" +
                "\t",depId);
        List<Map<String,Object>> list = jpaNativeQuery.getList(querySql, null, null);

        Map<String, String> nameById = userList.stream()
                .collect(Collectors.toMap(
                        map -> (String) map.get("id"), // key为ID
                        map -> (String) map.get("name")  // value为姓名
                ));
        for (Map<String, Object> map : list){
            String ids =(String) map.get("inspectors");
            if (StringUtils.isNotBlank(ids)){
                String nameListString = Arrays.stream(ids.split(";"))
                        .map(idString -> {
                            // 去除空格并尝试从Map中获取姓名
                            String trimmedId = idString.trim();
                                return nameById.getOrDefault(trimmedId, trimmedId);
                        })
                        .collect(Collectors.joining(";"));
                map.put("inspectors",nameListString);
            }
        }
        return list;
    }

    /**
     * 敲门行动
     * @param depId
     * @return
     */
    @GetMapping("/getKnock")
    public List<Map<String,Object>> getKnock(@RequestParam("depId") String depId){
        String querySql = String.format("SELECT\n" +
                "\to.id,\n" +
                "\t'opendoor' AS sheetCode,\n" +
                "\to.belongSq,\n" +
                "\t( SELECT NAME FROM h_org_department WHERE id = o.belongSq ) AS sqName,\n" +
                "\to.belongWg,\n" +
                "\t( SELECT NAME FROM h_org_department WHERE id = o.belongWg ) AS wgName,\n" +
                "\to.relevanceHouse,\n" +
                "\to.relevanceBusiness,\n" +
                "CASE\n" +
                "\t\t\n" +
                "\t\tWHEN o.relevanceHouse IS NOT NULL THEN\n" +
                "\t\t( SELECT NAME FROM iknn4_house WHERE id = o.relevanceHouse ) \n" +
                "\t\tWHEN o.relevanceBusiness IS NOT NULL THEN\n" +
                "\t\t( SELECT NAME FROM iknn4_business WHERE id = o.relevanceBusiness ) ELSE NULL \n" +
                "\tEND AS relevanceName,\n" +
                "\tcast(o.openDoorDate as DATE) as openDoorDate,\n" +
                "\to.creater,\n" +
                "\t( SELECT NAME FROM h_org_user WHERE id = o.creater ) AS jcy \n" +
                "FROM\n" +
                "\th_org_department d\n" +
                "\tLEFT JOIN iknn4_opendoor o ON ( d.id = o.belongJd OR d.id = o.belongSq OR d.id = o.belongWg ) \n" +
                "WHERE\n" +
                "\td.id = '%s'",depId);
        return jpaNativeQuery.getList(querySql,null,null);
    }

    /**
     * 走访工作日志
     * @param
     * @return
     */
    @GetMapping("/getVisitWorkLog")
    public List<Map<String,Object>> getVisitWorkLog(@RequestParam("depId") String depId){
        String querySql = String.format("SELECT\n" +
                "\ti.id as id,\n" +
                "\t'interviewWorkLog' as sheetCode,\n" +
                "\ti.belongSq as sqId,\n" +
                "\t(select name from h_org_department where id = i.belongSq) as sqName,\n" +
                "\ti.belongWg as wgId,\n" +
                "\t(select name from h_org_department where id = i.belongWg) as wgName,\n" +
                "\ti.interviewObject,\n" +
                "\ti.theme,\n" +
                "\tDate( i.interviewTime ) AS interviewTime \n" +
                "FROM\n" +
                "\th_org_department d\n" +
                "\tLEFT JOIN iknn4_interviewworklog i ON (\n" +
                "\td.id = i.belongJd or d.id = i.belongSq or d.id =i.belongWg)\n" +
                "where d.id = '%s'",depId);
        return jpaNativeQuery.getList(querySql,null,null);
    }

    /**
     * 网格走访日志
     * @param depId
     * @return
     */
    @GetMapping("/getWgZfLog")
    public List<Map<String,Object>> getWgZfLog(@RequestParam("depId") String depId){
        String querySql = String.format("SELECT\n" +
                "\ti.belongWg,\n" +
                "\t(select name from h_org_department where id = i.belongWg) as wg,\n" +
                "\tcount(*) as total\n" +
                "FROM\n" +
                "\th_org_department d\n" +
                "\tLEFT JOIN iknn4_interviewworklog i ON (d.id = i.belongSq or d.id = i.belongWg )\n" +
                "\twhere d.id = '%s' and i.sequenceStatus <> 'DRAFT'\n" +
                "\tGROUP BY i.belongWg\n" +
                "\tHAVING  \n" +
                "\ti.belongWg IS NOT NULL AND i.belongWg <> ''" +
                "\tORDER BY count(*) desc"
                ,depId);
        return jpaNativeQuery.getList(querySql,null,null);
    }

    /**
     * 网格员走访日志
     * @param depId
     * @return
     */
    @GetMapping("/getWgyZfLog")
    public List<Map<String,Object>> getWgyZfLog(@RequestParam("depId") String depId){
        String querySql = String.format("SELECT\n" +
                "\ti.creater,\n" +
                "\t(select name from h_org_user where id = i.creater) as wgy,\n" +
                "\tcount(*) as total\n" +
                "FROM\n" +
                "\th_org_department d\n" +
                "\tLEFT JOIN iknn4_interviewworklog i ON (d.id = i.belongSq or d.id = i.belongWg )\n" +
                "\twhere d.id = '%s' and i.sequenceStatus <> 'DRAFT'\n" +
                "\tGROUP BY i.creater\n"+
                "\tORDER BY count(*) desc"
                ,depId);
        return jpaNativeQuery.getList(querySql,null,null);
    }

    @GetMapping("/getzbs")
    public List<Map<String,Object>> getzbs(@RequestParam("jdId") String jdId){
        String querySql = String.format("SELECT\n" +
                "\tmax( t1.NAME ) zbsname,\n" +
                "\tcount( 1 ) cnt \n" +
                "FROM\n" +
                "\th_org_department t1\n" +
                "\tLEFT JOIN ikhmj_chuishao_001 t2 ON t2.zbszbbm LIKE concat( '%%', t1.id, '%%' ) \n" +
                "WHERE\n" +
                "\tt1.parentId = '%s' \n" +
                "\tAND t1.querycode LIKE '1_1#2_1#979182861#979182862%%' \n" +
                "\tAND !(\n" +
                "\t\tREGEXP_like(t1.NAME,'社区|村') \n" +
                "\t) \n" +
                "\tAND NOT REGEXP_like(t1.NAME,'网格') \n" +
                "\tAND t1.deleted = 0 \n" +
                "\tAND t2.jd_shangbao = '站办所处理' \n" +
                "\tAND t2.sequenceStatus <> 'DRAFT' \n" +
                "\tAND t2.eventStatus <> '删除' \n" +
                "GROUP BY\n" +
                "\tt1.id;\n",jdId);
        String queryrzbs = String.format("\tselect id,name from h_org_department where parentId = '%s' AND !(\n" +
                "\t\tREGEXP_like(NAME,'社区|村') \n" +
                "\t) \n" +
                "\tAND NOT REGEXP_like(NAME,'网格') \n" +
                "\tAND deleted = 0 ",jdId);
        List<Map<String,Object>> list = jpaNativeQuery.getList(querySql, null, null);
        List<Map<String,Object>> allMap = jpaNativeQuery.getList(queryrzbs, null, null);
        Set<String> existingNames = new HashSet<>();
        for (Map<String, Object> map : list) {
            existingNames.add((String) map.get("name"));
        }

        // 遍历所有names，添加缺失的name到queriedData中，cnt为0
        for (Map<String, Object> nameMap : allMap) {
            String name = (String) nameMap.get("name");
            if (!existingNames.contains(name)) {
                Map<String, Object> newData = new HashMap<>();
                newData.put("name", name);
                newData.put("cnt", 0);
                list.add(newData);
            }
        }

        return list;
    }

    /**
     * 街道干部联点
     */
    @GetMapping("/getStreetCadre")
    public List<Map<String,Object>> getStreetCadre(@RequestParam String depId){
        String querdep = String.format("SELECT id,name FROM `h_org_department`");
        String queyrSql = String.format("SELECT\n" +
                "\tid,\n" +
                "\t'jdgbldwgxxb' AS sheetCode,\n" +
                "\tssjd,\n" +
                "\t( SELECT NAME FROM h_org_department WHERE id = ssjd ) jdName,\n" +
                "\tsssq,\n" +
                "\t( SELECT NAME FROM h_org_department WHERE id = sssq ) sqName,\n" +
                "\tldwg,\n" +
                "\tjdgbName,\n" +
                "\tmobile,\n" +
                "\tposition \n" +
                "FROM\n" +
                "\t`iknn4_jdgbldwgxxb` \n" +
                "WHERE\n" +
                "\tldwg like  concat( '%%', '%s', '%%' )",depId);
        List<Map<String,Object>> list = jpaNativeQuery.getList(queyrSql, null, null);
        List<Map<String,Object>> depList = jpaNativeQuery.getList(querdep, null, null);
        Map<String, String> nameById = depList.stream()
                .collect(Collectors.toMap(
                        map -> (String) map.get("id"), // key为ID
                        map -> (String) map.get("name")  // value为姓名
                ));
        for (Map<String, Object> map : list){
            String ids =(String) map.get("ldwg");
            if (StringUtils.isNotBlank(ids)){
                String nameListString = Arrays.stream(ids.split(";"))
                        .map(idString -> {
                            // 去除空格并尝试从Map中获取姓名
                            String trimmedId = idString.trim();
                            return nameById.getOrDefault(trimmedId, trimmedId);
                        })
                        .collect(Collectors.joining(";"));
                map.put("ldwg",nameListString);
            }
        }
        return list;
    }

    /**
     * 事件情况
     * @param deptId
     * @param startDate
     * @param endDate
     * @param type
     * @return
     */
    @GetMapping("/getchuishao")
    public List<Map<String,Object>> getchuishao(@RequestParam("deptId") String deptId,@RequestParam(value = "startDate",required = false) String startDate,@RequestParam(value = "endDate",required = false) String endDate,@RequestParam("type") String type){
        if (StringUtils.isBlank(startDate)) {
            startDate = "1900-01-01 00:00:00";
        }
        if (StringUtils.isBlank(endDate)) {
            endDate = "2099-01-01 00:00:00";
        }
        if (StringUtils.isBlank(deptId)) {
            deptId = "2c9a8e6488e8fca60188e912ee421f97";
        }
        String finalQuerySql = "";
        if (deptId.equals("2c9a8e6488e8fca60188e912ee421f97")) {
            finalQuerySql = String.format("select c.id,c.sequenceNo,c.name as title,wi.id as workflowInstanceId, \n" + "case when c.sequenceStatus='COMPLETED' then '已完结' else '处理中' end as status, \n" + "concat(Dropdown1685712329221,'-',Dropdown1685712342343) as theme \n" + " FROM ikhmj_chuishao_001 c \n" + "LEFT JOIN h_org_user u ON c.owner = u.id \n" + "LEFT JOIN h_org_department d ON c.ownerDeptId = d.id \n" + "LEFT JOIN biz_workflow_instance wi ON c.workflowInstanceId = wi.id \n" + "WHERE ( c.eventStatus != '删除' or  c.eventStatus is null ) and c.sequenceStatus !='DRAFT' \n" + "and c.createdTime BETWEEN '%s' and  '%s'    \n", startDate, endDate);
        } else {
            finalQuerySql = String.format("select c.id,c.sequenceNo,c.name as title,wi.id as workflowInstanceId, \n" + "case when c.sequenceStatus='COMPLETED' then '已完结' else '处理中' end as status, \n" + "concat(Dropdown1685712329221,'-',Dropdown1685712342343) as theme \n" + " FROM ikhmj_chuishao_001 c \n" + "LEFT JOIN h_org_user u ON c.owner = u.id \n" + "LEFT JOIN h_org_department d ON c.ownerDeptId = d.id \n" + "LEFT JOIN biz_workflow_instance wi ON c.workflowInstanceId = wi.id \n" + "WHERE ( c.eventStatus != '删除' or  c.eventStatus is null ) and c.sequenceStatus !='DRAFT' \n" + "and c.createdTime BETWEEN '%s' and  '%s'    \n" + "and d.queryCode like (select concat(queryCode,'%s') from h_org_department where id = '%s')", startDate, endDate, "%", deptId);
        }
        switch (type) {
            case "全部":
                finalQuerySql +=" limit 500";
                break;
            case "区级":
                finalQuerySql += " and  eventStatus ='吹哨' limit 500";
                break;
            case "居民":
                finalQuerySql += " and  Radio1698973951943 = '是'  limit 500";
                break;
        }
        return jpaNativeQuery.getList(finalQuerySql,null,null);
    }

    /**
     * 获取街道下所有网格
     */
    @GetMapping("/getAllGridsByStreet")
    public List<Map<String,Object>> getAllGridsByStreet(){
        String querySql = String.format("SELECT\n" +
                "\tt1.jdid,\n" +
                "\tt5.name jdname,\n" +
                "\tTRIM( REPLACE ( REPLACE ( t1.sqid, '号网格', '' ), '网格', '' ) ) as sqid,\n" +
                "\tt2.id wgid,\n" +
                "\tt2.NAME AS wgname,\n" +
                "\tt1.jwd \n" +
                "FROM\n" +
                "\tilrv7_jwdsj t1\n" +
                "\tLEFT JOIN h_org_department t2 ON t1.bm = t2.id\n" +
                "\tLEFT JOIN h_org_department t3 ON t2.parentId = t3.id\n" +
                "\tLEFT JOIN h_org_department t4 ON t3.parentId = t4.id \n" +
                "\tleft join h_org_department t5 on t1.jdid = t5.id\n" +
                "\tWHERE\n" +
                "\tt5.parentId = '2c9a8e6488e8fca60188e912ee421f97'\n" +
                "\tAND t4.id <> '2c9a8e6488e8fca60188e912ee421f97' \n" +
                "\tAND t2.deleted = 0 \n" +
                "\tAND t3.deleted = 0 \n" +
                "\tAND t4.DELETEd = 0\n" +
                "\tORDER BY t1.jdid");
        List<Map<String,Object>> list = jpaNativeQuery.getList(querySql, null, null);
        Map<String,String> colorMap = new HashMap<>();
        colorMap.put("2c9a8e6488ebff8b0188f032498f02da","#a2a5ca");//通泰街
        colorMap.put("2c9a8e6488ebff8b0188f03312ab02e0","#7ab5fa");//望麓园
        colorMap.put("2c9a8e6488ebff8b0188f02d7dab02c8","#f4c66a");//清水塘
        colorMap.put("2c9a8e6488ebff8b0188f035005602ec","#70daa9");//湘雅路
        colorMap.put("2c9a8e6488ebff8b0188f0251e4a02a9","#f5ce7d");//东风路
        colorMap.put("2c9a8e6488ebff8b0188f037f2300304","#f37b82");//新河
        colorMap.put("2c9a8e6488ebff8b0188f0344c0d02e6","#f7ef93");//伍家岭
        colorMap.put("2c9a8e6488ebff8b0188f031193e02d4","#7bc8ee");//四方坪
        colorMap.put("2c9a8e6488ebff8b0188f036795902f8","#8ce4b1");//月湖
        colorMap.put("2c9a8e6488ebff8b0188f027de7802b6","#b975bb");//洪山
        colorMap.put("2c9a8e6488ebff8b0188f0372e0f02fe","#f7a79d");//浏阳河
        colorMap.put("2c9a8e6488ebff8b0188f0266ec402b0","#7ab47d");//芙蓉北路
        colorMap.put("2c9a8e6488ebff8b0188f028b92c02bc","#89bdfa");//捞刀河
        colorMap.put("2c9a8e6488ebff8b0188f035c5ec02f2","#eaaba0");//秀峰
        colorMap.put("2c9a8e6488ebff8b0188f02f465202ce","#acafd0");//沙坪
        colorMap.put("2c9a8e6488ebff8b0188f02bda8702c2","#9cb2e7");//青竹湖
        for(Map<String, Object> map : list){
            map.put("color",colorMap.get(map.get("jdid")));
        }
        return list;
    }
    /**
     * 已整改未发现
     * @param depId
     * @return
     */
    @GetMapping("/getyfxwzg")
    public List<Map<String,Object>> getyfxwzg(@RequestParam String depId){
        String querUser = String.format("\tselect id,name from h_org_user");
        List<Map<String,Object>> userList = jpaNativeQuery.getList(querUser,null,null);
        String querySql = String.format("SELECT DISTINCT\n" +
                "\ts.id,\n" +
                "\ts.sequenceNo,\n" +
                "\t'securityInspection_test' AS sheetCode,\n" +
                "\ts.belongJd,\n" +
                "\ts.belongWg,\n" +
                "\ts.belongSq,\n" +
                "\ts.inspectionType,\n" +
                "\ts.businessCategory,\n" +
                "\tCOALESCE ( ( SELECT NAME FROM iknn4_business WHERE id = s.relevanceBusiness ), ( SELECT NAME FROM iknn4_school WHERE id = s.relevanceSchool ), '' ) AS relevanceBusiness,\n" +
                "\ts.headName,\n" +
                "\ts.headMobile,\n" +
                "\ts.businessAddress,\n" +
                "CASE\n" +
                "\t\t\n" +
                "\t\tWHEN JSON_VALID( s.businessAddress ) \n" +
                "\t\tAND JSON_CONTAINS_PATH( s.businessAddress, 'one', '$.address' ) THEN\n" +
                "\t\t\tJSON_UNQUOTE(\n" +
                "\t\t\t\tJSON_EXTRACT( s.businessAddress, '$.address' )) ELSE JSON_UNQUOTE(\n" +
                "\t\t\tJSON_EXTRACT( s.businessAddress, '$.valString' )) \n" +
                "\t\tEND AS address,\n" +
                "\t\ts.createdDeptId,\n" +
                "\t\t( SELECT NAME FROM h_org_department WHERE id = s.createdDeptId ) jxdw,\n" +
                "\t\ts.inspectors,\n" +
                "\t\ts.inspectionContent,\n" +
                "\t\ts.inspectionTime,\n" +
                "\t\ts.inspectionPeopleNumber,\n" +
                "\t\ts.penaltyAmount \n" +
                "\tFROM\n" +
                "\t\th_org_department d\n" +
                "\t\tLEFT JOIN `iknn4_securityinspection_test` s ON ( d.id = s.belongJd OR d.id = s.belongSq OR d.id = s.belongWg ) \n" +
                "WHERE\n" +
                "\ts.id IN ( SELECT DISTINCT parentid FROM iknn4_securityinspection_child_t WHERE inspectionResult = '已发现' AND rectificationStage = '未整改' )" +
                "\tAND d.id = '%s'",depId);
        if ("2c9a8e6488e8fca60188e912ee421f97".equals(depId)||StringUtils.isBlank(depId)){
            querySql = String.format("SELECT DISTINCT\n" +
                    "\ts.id,\n" +
                    "\ts.sequenceNo,\n" +
                    "\t'securityInspection_test' AS sheetCode,\n" +
                    "\ts.belongJd,\n" +
                    "\ts.belongWg,\n" +
                    "\ts.belongSq,\n" +
                    "\ts.inspectionType,\n" +
                    "\ts.businessCategory,\n" +
                    "\tCOALESCE ( ( SELECT NAME FROM iknn4_business WHERE id = s.relevanceBusiness ), ( SELECT NAME FROM iknn4_school WHERE id = s.relevanceSchool ), '' ) AS relevanceBusiness,\n" +
                    "\ts.headName,\n" +
                    "\ts.headMobile,\n" +
                    "\ts.businessAddress,\n" +
                    "CASE\n" +
                    "\t\t\n" +
                    "\t\tWHEN JSON_VALID( s.businessAddress ) \n" +
                    "\t\tAND JSON_CONTAINS_PATH( s.businessAddress, 'one', '$.address' ) THEN\n" +
                    "\t\t\tJSON_UNQUOTE(\n" +
                    "\t\t\t\tJSON_EXTRACT( s.businessAddress, '$.address' )) ELSE JSON_UNQUOTE(\n" +
                    "\t\t\tJSON_EXTRACT( s.businessAddress, '$.valString' )) \n" +
                    "\t\tEND AS address,\n" +
                    "\t\ts.createdDeptId,\n" +
                    "\t\t( SELECT NAME FROM h_org_department WHERE id = s.createdDeptId ) jxdw,\n" +
                    "\t\ts.inspectors,\n" +
                    "\t\ts.inspectionContent,\n" +
                    "\t\ts.inspectionTime,\n" +
                    "\t\ts.inspectionPeopleNumber,\n" +
                    "\t\ts.penaltyAmount \n" +
                    "\tFROM\n" +
                    "\t\th_org_department d\n" +
                    "\t\tLEFT JOIN `iknn4_securityinspection_test` s ON ( d.id = s.belongJd OR d.id = s.belongSq OR d.id = s.belongWg ) \n" +
                    "WHERE\n" +
                    "\ts.id IN ( SELECT DISTINCT parentid FROM iknn4_securityinspection_child_t WHERE inspectionResult = '已发现' AND rectificationStage = '未整改' )\n");
        }

        List<Map<String,Object>> list = jpaNativeQuery.getList(querySql, null, null);

        Map<String, String> nameById = userList.stream()
                .collect(Collectors.toMap(
                        map -> (String) map.get("id"), // key为ID
                        map -> (String) map.get("name")  // value为姓名
                ));
        for (Map<String, Object> map : list){
            String ids =(String) map.get("inspectors");
            if (StringUtils.isNotBlank(ids)){
                String nameListString = Arrays.stream(ids.split(";"))
                        .map(idString -> {
                            // 去除空格并尝试从Map中获取姓名
                            String trimmedId = idString.trim();
                            return nameById.getOrDefault(trimmedId, trimmedId);
                        })
                        .collect(Collectors.joining(";"));
                map.put("inspectors",nameListString);
            }
            String address =(String) map.get("businessAddress");
            if (StringUtils.isNotBlank(address)){
                JSONObject o = JSON.parseObject(address);
                if (o.containsKey("lat")&&o.containsKey("lng")){
                    map.put("lat",o.get("lat").toString());
                    map.put("lng",o.get("lng").toString());
                }
            }
        }
        List<Map<String, Object>> result = list.stream().filter(map -> map.containsKey("lat")).collect(Collectors.toList());
        return result;
    }

    public  String extractGridRange(List<Map<String, Object>> list) {
        // 过滤出包含“号网格”的name，提取数字，并找到最小和最大的数字
        OptionalInt min = list.stream()
                .map(map -> (String) map.get("name"))
                .filter(name -> name != null && name.matches("\\d+号网格")) // 匹配包含数字的“号网格”
                .mapToInt(name -> Integer.parseInt(name.replaceAll("号网格", ""))) // 提取数字并转换为int
                .min();

        OptionalInt max = list.stream()
                .map(map -> (String) map.get("name"))
                .filter(name -> name != null && name.matches("\\d+号网格"))
                .mapToInt(name -> Integer.parseInt(name.replaceAll("号网格", "")))
                .max();

        // 如果找到了最小和最大数字，则拼接成范围字符串，否则返回空字符串
        if (min.isPresent() && max.isPresent()) {
            return min.getAsInt() + "-" + max.getAsInt() + "号网格";
        } else {
            return "";
        }
    }
}
